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

[longlong625]

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

 

[longlong625]

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.

 

[Ragdyer]

The proper syntax is:

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

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
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]

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

HTH
--
AP

<[email protected]> a écrit dans le message de (e-mail address removed)...
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.

 

[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))