Referencing formula using Indirect
Hi
Try
=INDIRECT("'"&$A$1&"'!R"&ROWS($1:4))
--
Regards
Roger Govier
Raj wrote:
I had tried this earlier. But when I copy the formula down the column,
the R4 does not become relative. eg. If the formula is in cell B3,
and is referencing R4, then I want the formula to reference R5 in
cell B4. But it references R4 only. How do I make the "R4" relative?
Thanks and Regards,
Rajendra
On Apr 25, 5:10 pm, "Ashish Mathur" wrote:
Hi,
Try this
=INDIRECT("'"&$A$1&"'!R4")
--
Regards,
Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com
"Raj" wrote in message
...
Hi,
A workbook has 13 sheets, a sheet each for the months from Apr-10 to
Mar-11 and final summary sheet
In the Summary sheet, I have a validation list in cell A1 where the
names of one of the sheets is selected by the user. eg, May-10
I want a formula to retrieve the value of a cell from that sheet (ie
May-10), eg Cell R4. The formula should have a relative address of the
source cell, so that when copied down, other cells from the source
sheet should be displayed.
Currently, I am using the following formula:
=INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4) ,1))
The formula works fine, except that the R4 refers to R4 in the Summary
sheet and not the month displayed in cell A1. Hence, any rows inserted/
deleted in the Summary sheet results in the REF error. Is there an
easy/another way to refer to cell R4 of the sheet whose name is
displayed in cell A1 of the current sheet?
Thanks in Advance for the help.
Regards,
Raj
|