Hi,

Try this

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.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