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" <rspai9@gmail.com> wrote in message 
news:094ca5aa-0492-4a7f-b461-65044b43de8d@y38g2000prb.googlegroups.com...
> 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=A0pm, "Ashish Mathur" <mathurash...@hotmail.com> wrote:
> Hi,
>
> Try this
>
> =3DINDIRECT("'"&$A$1&"'!R4")
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVPwww.ashishmathur.com
>
> "Raj" <rsp...@gmail.com> wrote in message
>
> news:094ca5aa-0492-4a7f-b461-65044b43de8d@y38g2000prb.googlegroups.com...
>
> > Hi,
>
> > A workbook has 13 sheets, a sheet each for the months from Apr-10 to
> > Mar-11 =A0and =A0final 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:
> > =3DINDIRECT("'"&$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" <mathurash...@hotmail.com> wrote:
>> Hi,
>>
>> Try this
>>
>> =INDIRECT("'"&$A$1&"'!R4")
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVPwww.ashishmathur.com
>>
>> "Raj" <rsp...@gmail.com> wrote in message
>>
>> news:094ca5aa-0492-4a7f-b461-65044b43de8d@y38g2000prb.googlegroups.com...
>>
>>> 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=A0pm, Roger Govier <ro...@technology4nospamu.co.uk>
wrote:
> Hi
>
> Try
> =3DINDIRECT("'"&$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 =A0in cell B3,
> > and is referencing R4, =A0then 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" <mathurash...@hotmail.com> wrote:
> >> Hi,
>
> >> Try this
>
> >> =3DINDIRECT("'"&$A$1&"'!R4")
>
> >> --
> >> Regards,
>
> >> Ashish Mathur
> >> Microsoft Excel MVPwww.ashishmathur.com
>
> >> "Raj" <rsp...@gmail.com> wrote in message
>
> >>news:094ca5aa-0492-4a7f-b461-65044b43de8d@y38g2000prb.googlegroups.com.=
...
>
> >>> Hi,
> >>> A workbook has 13 sheets, a sheet each for the months from Apr-10 to
> >>> Mar-11 =A0and =A0final 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 th=
e
> >>> source cell, so that when copied down, other cells from the source
> >>> sheet should be displayed.
> >>> Currently, I am using the following formula:
> >>> =3DINDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(R4),COLUMN(R4),1))
> >>> The formula works fine, except that the R4 refers to R4 in the Summar=
y
> >>> sheet and not the month displayed in cell A1. Hence, any rows inserte=
d/
> >>> 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