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

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

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

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

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

Regards,
Rajendra



On Apr 25, 8:59*pm, Roger Govier
wrote:
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