How to reference another cell's content directly in a formula

[longlong625@gmail.com]

Hi all,

For a cell such as that in A2 below:

A B
1 4Q 2005 1Q 2006
2 ='4Q 2005'!$R$7
3

The '4Q 2005' refers to a sheet in the same excel work book with that
name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'.
Is there any function I can use so that cell A2 uses the data in cell
A1 to figure out which sheet name to look for? I'm doing this so that
I can copy and paste cell A2 into cell B2 and don't have to manually
input '1Q 2006'.

Something to the effect of the pseudocode = 'content_of(A1)'!$R$7.
When copied and pasted to cell B2, the pseudocode would read =
'content_of(B1)'!$R$7.

Thanks.

[Ardus Petus]

Lokk at INDIRECT in Help

HTH
--
AP

<longlong625@gmail.com> a écrit dans le message de news:
1150913956.994699.64300@y41g2000cwy.googlegroups.com...
> Hi all,
>
> For a cell such as that in A2 below:
>
> A B
> 1 4Q 2005 1Q 2006
> 2 ='4Q 2005'!$R$7
> 3
>
> The '4Q 2005' refers to a sheet in the same excel work book with that
> name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'.
> Is there any function I can use so that cell A2 uses the data in cell
> A1 to figure out which sheet name to look for? I'm doing this so that
> I can copy and paste cell A2 into cell B2 and don't have to manually
> input '1Q 2006'.
>
> Something to the effect of the pseudocode = 'content_of(A1)'!$R$7.
> When copied and pasted to cell B2, the pseudocode would read =
> 'content_of(B1)'!$R$7.
>
> Thanks.
>

[longlong625@gmail.com]

This is not the way I want it to work since it requires the entire
reference to be in the cell A1. I need it so that it extracts the
string contained in cell A1, which is "2Q 2006" and place it in the
formula in cell A2 rather than automatically jump into the address
contained in cell A1. The appropriate function would be:

=3D'indirect(A1)'!$R$7 in cell A2

However, the above function does not work so I'm trying to find a way
to tweak it so that it will work.


Ardus Petus wrote:
> Lokk at INDIRECT in Help
>
> HTH
> --
> AP
>
> <longlong625@gmail.com> a =E9crit dans le message de news:
> 1150913956.994699.64300@y41g2000cwy.googlegroups.com...
> > Hi all,
> >
> > For a cell such as that in A2 below:
> >
> > A B
> > 1 4Q 2005 1Q 2006
> > 2 =3D'4Q 2005'!$R$7
> > 3
> >
> > The '4Q 2005' refers to a sheet in the same excel work book with that
> > name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'.
> > Is there any function I can use so that cell A2 uses the data in cell
> > A1 to figure out which sheet name to look for? I'm doing this so that
> > I can copy and paste cell A2 into cell B2 and don't have to manually
> > input '1Q 2006'.
> >
> > Something to the effect of the pseudocode =3D 'content_of(A1)'!$R$7.
> > When copied and pasted to cell B2, the pseudocode would read =3D
> > 'content_of(B1)'!$R$7.
> >
> > Thanks.
> >

[Ragdyer]

The proper syntax is:

=INDIRECT("'"&A1&"'!$R$7")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
<longlong625@gmail.com> wrote in message
news:1150917100.959695.39810@p79g2000cwp.googlegroups.com...
This is not the way I want it to work since it requires the entire
reference to be in the cell A1. I need it so that it extracts the
string contained in cell A1, which is "2Q 2006" and place it in the
formula in cell A2 rather than automatically jump into the address
contained in cell A1. The appropriate function would be:

='indirect(A1)'!$R$7 in cell A2

However, the above function does not work so I'm trying to find a way
to tweak it so that it will work.


Ardus Petus wrote:
> Lokk at INDIRECT in Help
>
> HTH
> --
> AP
>
> <longlong625@gmail.com> a écrit dans le message de news:
> 1150913956.994699.64300@y41g2000cwy.googlegroups.com...
> > Hi all,
> >
> > For a cell such as that in A2 below:
> >
> > A B
> > 1 4Q 2005 1Q 2006
> > 2 ='4Q 2005'!$R$7
> > 3
> >
> > The '4Q 2005' refers to a sheet in the same excel work book with that
> > name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'.
> > Is there any function I can use so that cell A2 uses the data in cell
> > A1 to figure out which sheet name to look for? I'm doing this so that
> > I can copy and paste cell A2 into cell B2 and don't have to manually
> > input '1Q 2006'.
> >
> > Something to the effect of the pseudocode = 'content_of(A1)'!$R$7.
> > When copied and pasted to cell B2, the pseudocode would read =
> > 'content_of(B1)'!$R$7.
> >
> > Thanks.
> >

[Ragdyer]

Actually, since the cell reference is literally 'text', you can forget about
the absolutes:

=INDIRECT("'"&A1&"'!R7")

However, if you need to actually increment *both* the sheet *and* the cell,
you could try this:

=INDEX(INDIRECT("'"&A1&"'!R:R"),ROWS($1:7))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:OcOAeeWlGHA.3336@TK2MSFTNGP05.phx.gbl...
> The proper syntax is:
>
> =INDIRECT("'"&A1&"'!$R$7")
>
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> <longlong625@gmail.com> wrote in message
> news:1150917100.959695.39810@p79g2000cwp.googlegroups.com...
> This is not the way I want it to work since it requires the entire
> reference to be in the cell A1. I need it so that it extracts the
> string contained in cell A1, which is "2Q 2006" and place it in the
> formula in cell A2 rather than automatically jump into the address
> contained in cell A1. The appropriate function would be:
>
> ='indirect(A1)'!$R$7 in cell A2
>
> However, the above function does not work so I'm trying to find a way
> to tweak it so that it will work.
>
>
> Ardus Petus wrote:
>> Lokk at INDIRECT in Help
>>
>> HTH
>> --
>> AP
>>
>> <longlong625@gmail.com> a écrit dans le message de news:
>> 1150913956.994699.64300@y41g2000cwy.googlegroups.com...
>> > Hi all,
>> >
>> > For a cell such as that in A2 below:
>> >
>> > A B
>> > 1 4Q 2005 1Q 2006
>> > 2 ='4Q 2005'!$R$7
>> > 3
>> >
>> > The '4Q 2005' refers to a sheet in the same excel work book with that
>> > name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'.
>> > Is there any function I can use so that cell A2 uses the data in cell
>> > A1 to figure out which sheet name to look for? I'm doing this so that
>> > I can copy and paste cell A2 into cell B2 and don't have to manually
>> > input '1Q 2006'.
>> >
>> > Something to the effect of the pseudocode = 'content_of(A1)'!$R$7.
>> > When copied and pasted to cell B2, the pseudocode would read =
>> > 'content_of(B1)'!$R$7.
>> >
>> > Thanks.
>> >
>

[Ardus Petus]

=INDIRECT("'"&a1&"'!$r$7")

HTH
--
AP

<longlong625@gmail.com> a écrit dans le message de news:
1150917100.959695.39810@p79g2000cwp.googlegroups.com...
This is not the way I want it to work since it requires the entire
reference to be in the cell A1. I need it so that it extracts the
string contained in cell A1, which is "2Q 2006" and place it in the
formula in cell A2 rather than automatically jump into the address
contained in cell A1. The appropriate function would be:

='indirect(A1)'!$R$7 in cell A2

However, the above function does not work so I'm trying to find a way
to tweak it so that it will work.


Ardus Petus wrote:
> Lokk at INDIRECT in Help
>
> HTH
> --
> AP
>
> <longlong625@gmail.com> a écrit dans le message de news:
> 1150913956.994699.64300@y41g2000cwy.googlegroups.com...
> > Hi all,
> >
> > For a cell such as that in A2 below:
> >
> > A B
> > 1 4Q 2005 1Q 2006
> > 2 ='4Q 2005'!$R$7
> > 3
> >
> > The '4Q 2005' refers to a sheet in the same excel work book with that
> > name, so therefore cell A2 is set equal to cell R7 of sheet '4Q 2005'.
> > Is there any function I can use so that cell A2 uses the data in cell
> > A1 to figure out which sheet name to look for? I'm doing this so that
> > I can copy and paste cell A2 into cell B2 and don't have to manually
> > input '1Q 2006'.
> >
> > Something to the effect of the pseudocode = 'content_of(A1)'!$R$7.
> > When copied and pasted to cell B2, the pseudocode would read =
> > 'content_of(B1)'!$R$7.
> >
> > Thanks.
> >