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?


--
amaranth
------------------------------------------------------------------------
amaranth's Profile: http://www.excelforum.com/member.php...o&userid=26031
View this thread: http://www.excelforum.com/showthread...hreadid=393812

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL


"amaranth" wrote in
message ...

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?


--
amaranth
------------------------------------------------------------------------
amaranth's Profile:
http://www.excelforum.com/member.php...o&userid=26031
View this thread: http://www.excelforum.com/showthread...hreadid=393812

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


"KL" wrote in message ...
Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL


"amaranth" wrote in
message ...

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?


--
amaranth
------------------------------------------------------------------------
amaranth's Profile:
http://www.excelforum.com/member.php...o&userid=26031
View this thread: http://www.excelforum.com/showthread...hreadid=393812

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" wrote in message
...
For when C5 is a variable you can simply use:
=INDIRECT("'"&A2&"'!" & C5)
instead of:
=INDIRECT("'"&A2&"'!"&CELL("address",C5))


"KL" wrote in message
...
Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL


"amaranth" wrote
in
message ...

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?


--
amaranth
------------------------------------------------------------------------
amaranth's Profile:
http://www.excelforum.com/member.php...o&userid=26031
View this thread:
http://www.excelforum.com/showthread...hreadid=393812

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...2.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


"KL" wrote in message ...
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" wrote in message
...
For when C5 is a variable you can simply use:
=INDIRECT("'"&A2&"'!" & C5)
instead of:
=INDIRECT("'"&A2&"'!"&CELL("address",C5))


"KL" wrote in message
...
Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL


"amaranth" wrote
in
message ...

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?


--
amaranth
------------------------------------------------------------------------
amaranth's Profile:
http://www.excelforum.com/member.php...o&userid=26031
View this thread:
http://www.excelforum.com/showthread...hreadid=393812

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

Regards,
KL


"David McRitchie" wrote in message
...
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...2.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


"KL" wrote in message
...
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" wrote in message
...
For when C5 is a variable you can simply use:
=INDIRECT("'"&A2&"'!" & C5)
instead of:
=INDIRECT("'"&A2&"'!"&CELL("address",C5))


"KL" wrote in message
...
Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL


"amaranth"
wrote
in
message ...

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?


--
amaranth
------------------------------------------------------------------------
amaranth's Profile:
http://www.excelforum.com/member.php...o&userid=26031
View this thread:
http://www.excelforum.com/showthread...hreadid=393812