Lookup cell value using list of worksheet names

[amaranth]

I've got a workbook with 80 worksheets in. The first worksheet (INDEX)
has a vertical list of all the worksheet names. I want to lookup a cell
in each of the worksheets using the vertical list and return it to a
column on the INDEX sheet. However, I don't want to use a VLOOKUP as
this would be time consuming. What I'd ideally like is a formula along
the lines of:

='A2'!C5

where A2 is one of the worksheet names, and C5 is the cell on that
worksheet that I want to return. Is there any easy way to do this?

 

[KL]

Hi,

Try this:

=INDIRECT("'"&A2&"'!C5")

or this (if the C5 reference is variable):

=INDIRECT("'"&A2&"'!"&CELL("address",C5))

Regards,
KL

 

[David McRitchie]

For when C5 is a variable you can simply use:
=INDIRECT("'"&A2&"'!" & C5)
instead of:
=INDIRECT("'"&A2&"'!"&CELL("address",C5))

 

[KL]

Hi David,

I actually meant the variability of the cell's address (i.e. relative
reference) not the value - excuse my French
Your formula requires the cell reference to be a value of the cell C5.

Regards,
KL

 

[David McRitchie]

Ahh, yes, your formulas are the same the difference being that
the one with Address can be used with the fill handle to fill down
while the first one had the address in quotes so fill handle would
not work. My mistake in thinking you were supplying two different
purposes.

I had used the CELL with "address" for that purpose before but
now that you brought it up I'd not realized why HYPERLINK Worksheet
Formula was a bit more complicated when used with INDIRECT than
I had used -- obviously wasn't using fill down.
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm#indirect

 

[KL]

Yup, that's it. Thanks for coming back.

Regards,
KL

Ahh, yes, your formulas are the same the difference being that
the one with Address can be used with the fill handle to fill down
while the first one had the address in quotes so fill handle would
not work. My mistake in thinking you were supplying two different
purposes.

I had used the CELL with "address" for that purpose before but
now that you brought it up I'd not realized why HYPERLINK Worksheet
Formula was a bit more complicated when used with INDIRECT than
I had used -- obviously wasn't using fill down.
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm#indirect
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi David,

I actually meant the variability of the cell's address (i.e. relative
reference) not the value - excuse my French
Your formula requires the cell reference to be a value of the cell C5.

Regards,
KL