Is it possible to have a sheet reference in a formula that is based on a
referenced cell?

For example, if I have the following formula in cell A1(Sheet1):
=Sheet2!A1, then I have a list of Sheets ie. Sheet 3, Sheet 4, Sheet 5
in cells C1:C3 respectively, can I create a formula that will look at
C1:C3 for the corresponding "sheet reference" to look up?

Many thanks!


--
atran
------------------------------------------------------------------------
atran's Profile: http://www.excelforum.com/member.php...o&userid=24540
View this thread: http://www.excelforum.com/showthread...hreadid=381592

Look at INDIRECT in Help.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"atran" wrote in
message ...

Is it possible to have a sheet reference in a formula that is based on a
referenced cell?

For example, if I have the following formula in cell A1(Sheet1):
=Sheet2!A1, then I have a list of Sheets ie. Sheet 3, Sheet 4, Sheet 5
in cells C1:C3 respectively, can I create a formula that will look at
C1:C3 for the corresponding "sheet reference" to look up?

Many thanks!


--
atran
------------------------------------------------------------------------
atran's Profile:
http://www.excelforum.com/member.php...o&userid=24540
View this thread: http://www.excelforum.com/showthread...hreadid=381592

Try this:

=INDIRECT(C1&"!A1")

And copy down.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"atran" wrote in
message ...

Is it possible to have a sheet reference in a formula that is based on a
referenced cell?

For example, if I have the following formula in cell A1(Sheet1):
=Sheet2!A1, then I have a list of Sheets ie. Sheet 3, Sheet 4, Sheet 5
in cells C1:C3 respectively, can I create a formula that will look at
C1:C3 for the corresponding "sheet reference" to look up?

Many thanks!


--
atran
------------------------------------------------------------------------
atran's Profile:
http://www.excelforum.com/member.php...o&userid=24540
View this thread: http://www.excelforum.com/showthread...hreadid=381592

Try...

A1, copied down:

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

...where C1 contains the sheet name.

Hope this helps!

atran Wrote:
Is it possible to have a sheet reference in a formula that is based on a
referenced cell?

For example, if I have the following formula in cell A1(Sheet1):
=Sheet2!A1, then I have a list of Sheets ie. Sheet 3, Sheet 4, Sheet 5
in cells C1:C3 respectively, can I create a formula that will look at
C1:C3 for the corresponding "sheet reference" to look up?

Many thanks!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381592

Domenic, Unfortunately your formula did not work for me, any ideas?

Thanks!


--
atran
------------------------------------------------------------------------
atran's Profile: http://www.excelforum.com/member.php...o&userid=24540
View this thread: http://www.excelforum.com/showthread...hreadid=381592

RagDyeR,
Your formula worked well - however, is there a way the A1 portion can
be a relative reference as I pull down?

Thanks!


--
atran
------------------------------------------------------------------------
atran's Profile: http://www.excelforum.com/member.php...o&userid=24540
View this thread: http://www.excelforum.com/showthread...hreadid=381592

Try...

=INDIRECT(C1&"!"&CELL("address",A1))

OR

=INDIRECT("'"&C1&"'!"&CELL("address",A1))

...if your sheet name contains a space.

Hope this helps!

atran Wrote:
RagDyeR,
Your formula worked well - however, is there a way the A1 portion can
be a relative reference as I pull down?

Thanks!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=381592