MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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)?

Posted by Jacob on January 03, 2002 3:45 PM


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.



Posted by IML on January 03, 2002 3:47 PM

Just adding to what Jacob said, the format would be


where you enter in cell A1 the active sheet the row you would like to go to.