Active Column Ref In a Formula #2
My problem is summarised as follows:
A1 to, say, G1 contains a list of numbers
In A1 =A1
In B1 =Sum(A1:B1)
In C1 =Sum(A1:C1)
In D1 =Sum(A1:D1) etc... to =Sum(A1:G1)
Is it possible to have a formula which can datermine the column reference of
the cell containing the formula?
Some common element that replaces B1, C1, D1 etc. in the above example?
If you insert:
=SUM(INDIRECT("A1:" & SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()-1),3)),"$","") &
you will get the sum of the values from A1 thru just befor...help me with excel formula please
A B can somebody help with a formula in the B colunm so
1 1 give values added upwards in the A colunm
4 ( 1+2+3+4)
In B1 enter:
In B2 enter:
=B1+A2 and copy down
Gary''s Student - gsnu200903
> A B can somebody help with a formula in the B colunm so
> that it
> 1 1 give values added upwards in the A colunm
> 2 (1+2)
> 3 (1+2+3)
> 4 ( 1+2+3+4)
> ...UDF to Convert formula results to text
Can a UDF be used to convert formula results from column A to text in
I know that I can manually cut and paste "special" to achieve this, but I
want to avoid a manual step.
Instead of a UDF use a macro. Change columns(2) to columns(1) to change the
column withOUT the need for an additional column.
Columns(2).Value = Columns(1).Value
Microsoft MVP Excel
"Don" <email@example.com> wrote in message
> Can a UD...Beginner(formulas?)
Hey..im doing this add math project of mine and im really new to
Lets say i have the numbers 1 - 10 in column A, and i have to count
them using the sum 4.2+2X (X representing the respective numbers in A).
WHat formula do i have to use? and how do i duplicate the same formula
for each of these numbers.
1 4.2+2(1) = 6.2
the thing is, the question requires me to do the same thing for a
hundred numbers..and its a tougher sum..so i need help..thanx
I would like to delete the dashes in a numeric string but keep the numbers intact. ex 0777-3333-5 to 077733335
Edit | Replace replace dash with nothing.
Be careful: If you only select one cell while enacting this command, XL
will search your entire worksheet. If you want just one cell, choose the
one cell and a blank cell.
"jose" <firstname.lastname@example.org> wrote in message
> I would like to delete the dashes in a numeric string but keep the numbers
intact. ex 0777-3333-5 to 0...Search Engine in an Excel SpreadSheet
Is there any way to include a search engine in a spreadsheet? I would like
the user to enter a word into a cell and maybe click on a button to search
the workbook much in the way a web search works?
Thanks in advance
Try this. Right click sheet tab>view code>copy/paste this>SAVE>type search
text into cell a1 of that sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
For Each ws In Worksheets
Set c = .Find(Target, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c....searching in non-adjacent cells
there is a column with values like
now the min() would give me 1 . I discard 1 and I want to search fo
the smallest value in the remaining values. how do I do this?
Please suggest a formula
betzi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1508
View this thread: http://www.excelforum.com/showthread.php?threadid=26718
what is the logic to skip values=? do you want to skip only '1'
"be...How do I find document by searching standard properties in Word 20
For 7 years I have been able to locate correspondence in Word 2000 by
searching for files where properties contain certain keywords, or where the
files text contains specific words. I know how to add properties to a Word
2007 file but cannot find out how to search for a particular file by using
those same properties, or by specifying specific text/words used in the file
itself. The MS help screens say you can search by property values, but do
not make is easy to find out how this can be done.
The easiest way I've found is by using the All Files option in the
older-style W...help with a formula #5
I have 98 sets of magazines each set has 12 in the set
what formula can I use to get a total of issues in all sets
have you tried 98*12
"Just Me" <email@example.com> wrote in message
>I have 98 sets of magazines each set has 12 in the set
> what formula can I use to get a total of issues in all sets
if the numbers are in A1 and A2
(remove nothere from email address if mailing direct)
"Just Me" <n...I have a formula in my workbook that was functioning but it doesn.
I have a simple formula that adds amounts. It was working all the time but
now suddenly it's not adding on, i.e. the formula is still in the field but
the result is not coming......
Can somebody help?
it could be that your numbers are formatted as text - copy a blank cell from
select your numbers and the formula and choose edit / paste special - add
then select the formula cell press F2 and then enter
alternatively, check that calculation is not set to manual - tools / options
/ calculation - ensure that automatic is selected
"Ivan&qu...Cell searching and Replacing
Hi Could use some help here please have a movie list
someone sent to me and it has cells like
Age of Innocence, The
Bone Snatcher, The
I would like to do a Next procedure and Replace the text
and put the The in Front of the text
So it is like this
The Age Of Innocence
The Bone Snatcher
Hope someone can be kind enough to help me
=IF(RIGHT(A1,5)=", The",RIGHT(A1,3)&" "&LEFT(A1,LEN(A1)-5),A1)
Hope it helps.
Graham Feeley Wrote:
> Hi Cou...Replacement for Calendar/Contacts Public Folders?
I own a Public Folders in our Exchange Server environment. Among other
things, we have a folder of Contacts (customers) and a Calendar folder
(vacations). I have been told by our Exchange IT people that they are
developing a migration plan away from Public Folders since Microsoft keeps
saying that they're going to drop them in some version of Exchange some day.
So, my question is this: what is the replacement strategy for Contact and
Calendar folders that integrated equally well with Outlook? Can SharePoint
do this as well or better?
It took a LONG time to get people to use the stu...time formula question
This formula works great if the ending time is before 0:00.
$C$9:$C$11 is my starting time i.e. 20:00
$E$9:$E$11 is my ending time i.e. 04:30
How can I get this to work if A21 = 20:15?
"David" <dfizer@r...Simple Search and Replace Question
Hi. I am searching and replacing various characters in an Excel column. I
need to delete all the asterisks (*) in this column. How do I do this? That
is, how do I make Excel "think" the asterisk is NOT a wildcard characteer but
a real character that I want to get rid of?
> Hi. I am searching and replacing various characters in an Excel column. I
> need to delete all the asterisks (*) in this column. How do I do this? That
> is, how do I make Excel "think" the asterisk is NOT a wildcard characteer but
> a real character that I want t...Help with Formula
I'm trying to count dates in a range in another worksheet (Test 1),
count is to be based on being euqal (=) to or less than (>) the date
that is entered in B10. If there is no date in B10 I want the cell to
The data in 'Tests 1'!AD3:AD1000 is entered and formatted as a date as
is the data in B10
I am using:
this only returns 0
Reality is the leading cause of stress amongst those in touch with it.
- Jane Wagner
=IF(B10=&quo...Linking to files with a formula
In Excel 2003 what is the trick to linking to a closed work sheet using a
I'd like to put a list of file names in column A then use a formula to ling
to cells from those books.
Straight link works ='C:\My Docs\[File_1.xls]Sheet1'!X1
Formula does not ='C:\My Docs\[&A4&]Sheet1'!X1
Indirect will not work because I have too many books.
Grateful for any help on this one!
Maybe you build your formulas with a macro
I use getopenfilename here but it is also possible to loop through the column with file names
http://www.rondebruin.nl/s...Want to Change Range Selected in Formula
I would like to pass a variable through a function. Based on the value, I
have some ranges already name ie. Dataquestion#. I would like to be able to
change the named range to use in this formula based on numbers, such as mQNo.
Any help would be appreciated.
mQNo = 1
Set mQuestionRange1 = Worksheets("Data").Range(Chr(34) & "DataQuestion" &
mQNo & Chr(34))
This is siomple. I don't think you understadn strings. You don't need
a second set of double quotes. It is that simple
Var1 = "abc"
Var2 = "def&q...Enter Formula without using =
I would like to be able to enter a formula into a cell and have it not
require me to first type the equals sign.
For example, when I type
I would like for the cell to return
and when I edit the cell, it would show
(e.g. Excel automatically inserts the = before the formula)
Does anyone know how to do this?
Tools>options>transition and select transition formula entry
"Brian Tankersley" <firstname.lastname@example.org> wrote in message
>I would like to be able to en...Inter sheet formula won't format properly
MS Excel 2003.
I am writing a formula with a reference to a cell in another sheet, and
Excel is behaving oddly. Sometimes it will give me the result, ie what
is in the target cell, but sometimes it gives me the formula.
The formula I am writing is simply
thereby trying to get cell A22 from the sheet called 2.
Why is Excel not always just giving me what's in the target cell?
Sometimes all I can see is ='2'!A22. It's driving me nuts.
I have looked at all the formatting options with no luck; I have toggled
(using Ctrl`) the formula view with the norma...Search
I was wondering where does IE 7 & IE 8 keep search provider info? If I
wanted to set a default for all machines in a domain without having to
touch each machine, is this something I could script or put in a batch
file? Or is there an active directory setting?
Internet Explorer Administration Kit - IEAK 8, IEAK 7, IEAK 6 | TechNet:
Group Policy Settings Reference for Windows Internet Explorer 8:
http://www.microsoft.com/downloads/details.aspx?FamilyID=ab4655f2-0a3c-42eb-974d-24b2790bf592&display...Search criteria between
I have a table containing let´s say 5 posts – A,B,C,D and E.
Then I make a selecting question with query conditions like “Between C and H”
The result then will be C,D and E.
If I want to make a report from this result and I in the header of this
report want to have
“This report contains posts between C and H”
How will I achive this ???
Thanks in advance for Your help
The usual way to give the user a way to select criteria is by unbound
controls on a form. In your case you need a way for the user to select C and
H. You could use a form named FrmCriteria with two co...Creating a conditional formula to increment #s in a test script
I have an Excel question. I am creating a test script template for our team
and I an in need of some formula help. Here is what I wish to do:
Let's say I have a column A in my test script. I wish to label this column
with my test script #
TC3...(and so on)
However, the problem is I have section breaks in my test script that divides
the document into sections: eg..
Now the problem is that if I want to add a new test case to the "Login
section" above, I need ...Don't know which formula to use!
If I have several sheets in a workbook, and I want one as the master to refer to all others so that when a number is placed in a cell on the master it is either subtracted or added from the same cell in another sheet, depending which sheet or sheets are named from the master sheet. Is this possible?
Could you explain what you mean in a bit more detail?
For instance, will the entry on the Master sheet be made in the same
cell each time? If not, will it be the same cell for each named sheet?
How will the name in the master sheet correspond to the value- to the
left? right? above? below? Ho...Please help with formula #2
It has been a long time since I've needed to write for help, but I need
it now. I had a friend come to me for help with a spreadsheet he's
creating for the food service company he works for. While I taught
advanced Excel in college, that was four years ago, and I don't recall
this particular issue.
The first worksheet is a payment summary sheet. Next, there are sheets
for (invoices) for each organization the food service company deals
with. On each invoice sheet, there are cells which identify information
common to each of these company sheets. All of the invoice sheets are
t...Data row wise, formula column wise
I have data in cells b2:aj2
I want to refer to these cells in a column from a2 down. I can manually type in:
Rather than doing so, is there a formula I can type in once in A2 then copy
If you just need the data in column A........
Select B2:AJ2 and copy.
Select A2 and Paste Special>Transpose>OK>Esc
Gord Dibben Excel MVP
On Wed, 7 Dec 2005 17:18:55 -0600, "Fred Smith" <email@example.com> wrote:
>I have data in cells b2:aj2
>I want to refer to these cells in a column from a2 down. I can manuall...