creating cell references from text values
Posted by (Mr) Lindsay Lorden on January 03, 2002 3:32 PM
I have a need to calculate average, median, etc values from a range of cells in a sheet.
At present I modify the formula manually each month to include the extra rows for the month. I'm looking for a way to be able to construct the cell reference from other cell variables.
e.g. my cell value for calculating the average is
=AVERAGE('DataSheet'!D4:D109)
However the "109" will increase over time. Next month it will be "113" i.e. I will want it to use
=AVERAGE('DataSheet'!D4:D113)
I have experimented with the ADDRESS function & coded
=ADDRESS( B19, 1, 4, TRUE, "DataSheet" ) where "B19" is the cell reference in this sheet that contains 109 for now. I would merely need to update this cell value to 113 for next month to effect the change.
However, all =ADDRESS(...) gives me is the text value of
'DataSheet'!A109
not the value of that cell reference.
Once I can get something like this working, I can then use this "address" or other format on some 56 cells to achieve my objectives.
Can anyone tell me how to get a changing row number to substitute (a "value of a value" pointer I guess)?
Posted by Jacob on January 03, 2002 3:45 PM
Hi
Indirect() is what you want. indirect will look at the cell that a cell value says so for example if A1 has "B1" in it and B1 = 5, then if you said in C1=A1 you would get "B1", but if you said C1-indirect(A1) you get 5. Put the indirect function in place of the range and say like indirect(A1) then just change the values in A1.
Also Im not 100% sure what you are doing but if you want to take the average of something where the rows will change try this = Sum(A1:A1000)/CountA(A1:A1000) this will take the average of only the cells with some value in them and ignore the blanks.
HTH
Jacob
Posted by IML on January 03, 2002 3:47 PM
Just adding to what Jacob said, the format would be
=AVERAGE(INDIRECT("DataSheet!D4:D"&a1))
where you enter in cell A1 the active sheet the row you would like to go to.
