Lookup cell value using list of worksheet names

**amaranth** · 08-08-2005, 05:26 AM

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 · 08-08-2005, 07:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 08-08-2005, 10:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 08-08-2005, 10:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

David McRitchie · 08-08-2005, 12:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 08-08-2005, 12:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-05-2005, 10:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-05-2005, 10:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-05-2005, 10:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-05-2005, 10:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-05-2005, 10:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-05-2005, 11:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 09-05-2005, 11:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-05-2005, 11:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-05-2005, 11:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-05-2005, 11:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 12:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 12:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 12:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 12:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 12:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-06-2005, 01:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 01:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 01:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 01:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 01:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 09-06-2005, 02:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 02:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 02:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 02:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 02:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-06-2005, 03:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 03:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 03:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 03:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 03:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 09-06-2005, 04:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

KL · 09-06-2005, 04:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 04:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

David McRitchie · 09-06-2005, 04:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 04:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-06-2005, 06:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 06:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 06:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 06:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 06:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 09-06-2005, 07:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

KL · 09-06-2005, 07:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 07:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 07:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 07:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 10:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 10:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 10:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 10:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 10:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 11:05 AM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 11:05 AM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 11:05 AM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 11:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

KL · 09-06-2005, 11:05 AM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 12:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

David McRitchie · 09-06-2005, 12:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 12:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 09-06-2005, 12:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-06-2005, 12:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

KL · 09-06-2005, 02:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 02:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 02:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 02:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 02:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-06-2005, 03:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 03:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 03:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

KL · 09-06-2005, 03:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 03:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

KL · 09-06-2005, 06:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

KL · 09-06-2005, 06:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 06:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

David McRitchie · 09-06-2005, 06:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 06:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

KL · 09-06-2005, 07:05 PM

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

Regards,
KL

"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > For when C5 is a variable you can simply use:
>> > =INDIRECT("'"&A2&"'!" & C5)
>> > instead of:
>> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >
>> >
>> > "KL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi,
>> >>
>> >> Try this:
>> >>
>> >> =INDIRECT("'"&A2&"'!C5")
>> >>
>> >> or this (if the C5 reference is variable):
>> >>
>> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>> >>
>> >> Regards,
>> >> KL
>> >>
>> >>
>> >> "amaranth" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > 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
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

KL · 09-06-2005, 07:05 PM

Hi,

Try this:

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

or this (if the C5 reference is variable):

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

Regards,
KL

"amaranth" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

David McRitchie · 09-06-2005, 07:05 PM

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

"KL" <[email protected]> wrote in message news:[email protected]...
> Hi,
>
> Try this:
>
> =INDIRECT("'"&A2&"'!C5")
>
> or this (if the C5 reference is variable):
>
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
> Regards,
> KL
>
>
> "amaranth" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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
> >
>
>

KL · 09-06-2005, 07:05 PM

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" <[email protected]> wrote in message
news:[email protected]...
> For when C5 is a variable you can simply use:
> =INDIRECT("'"&A2&"'!" & C5)
> instead of:
> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>
>
> "KL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Try this:
>>
>> =INDIRECT("'"&A2&"'!C5")
>>
>> or this (if the C5 reference is variable):
>>
>> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
>>
>> Regards,
>> KL
>>
>>
>> "amaranth" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > 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
>> >
>>
>>
>
>

David McRitchie · 09-06-2005, 07:05 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > For when C5 is a variable you can simply use:
> > =INDIRECT("'"&A2&"'!" & C5)
> > instead of:
> > =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >
> >
> > "KL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi,
> >>
> >> Try this:
> >>
> >> =INDIRECT("'"&A2&"'!C5")
> >>
> >> or this (if the C5 reference is variable):
> >>
> >> =INDIRECT("'"&A2&"'!"&CELL("address",C5))
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "amaranth" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > 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
> >> >
> >>
> >>
> >
> >
>
>

Lookup cell value using list of worksheet names

LinkBack

Thread Tools

Rate This Thread

Display

Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names

Re: Lookup cell value using list of worksheet names