Referencing formula using Indirect

[Raj]

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

 

[Ashish Mathur]

Hi,

Try this

=INDIRECT("'"&$A$1&"'!R4")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Raj]

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

 

[Roger Govier]

Hi

Try
=INDIRECT("'"&$A$1&"'!R"&ROWS($1:4))

 

[Raj]

Thanks a ton, Roger. It works exactly as it should.

Regards,
Rajendra