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
However the "109" will increase over time. Next month it will be "113" i.e. I will want it to use
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
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)?