help with vlookup across multiple worksheets

Hi,

I'm pretty new to using lookup functions. I have a workbook wiuth 96
worksheets, and rather than linking individual cells to each worksheet,
I was hoping I could use a lookup function. I entered the name of each
worksheet on my output page. Is there a way to get the lookup function
to read the data in that cell as the name of the worksheet? Any help
would be appreciated.

Thanks!

Hi!

Try something like this:

G1 = sheet name

=VLOOKUP(A1,INDIRECT("'"&G1&"'!a1:b5"),2,0)

Biff

"jnasr" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm pretty new to using lookup functions. I have a workbook wiuth 96
> worksheets, and rather than linking individual cells to each worksheet,
> I was hoping I could use a lookup function. I entered the name of each
> worksheet on my output page. Is there a way to get the lookup function
> to read the data in that cell as the name of the worksheet? Any help
> would be appreciated.
>
> Thanks!
>

Assuming your sheet name is in cell A5 and you want to return the value
of cell A1 from that sheet try:
=INDIRECT(A5&"!A1")

Hope this helps
Rowan

jnasr wrote:
> Hi,
>
> I'm pretty new to using lookup functions. I have a workbook wiuth 96
> worksheets, and rather than linking individual cells to each worksheet,
> I was hoping I could use a lookup function. I entered the name of each
> worksheet on my output page. Is there a way to get the lookup function
> to read the data in that cell as the name of the worksheet? Any help
> would be appreciated.
>
> Thanks!
>

to make it simpler, let's say I have sheet 1 and 2. On sheet 2, I have
data from cells b9 to c109. I'm trying to use the vlookup to reference
the data label in the first column of sheet 2 (column b in the array)
and lookup the data value in the second column (column c).

on the first sheet, i have the second sheet name in cell d3 and the
lookup reference in cell b3. I think Biff's suggestion may work, but I
don't know how to manipulate the indirect function. Excel's help tool
was pretty useless.

Thanks,
Joe

Try this:

=VLOOKUP(B3,INDIRECT("'"&D3&"'!B9:C109"),2,0)

Biff

"jnasr" <[email protected]> wrote in message
news:[email protected]...
> to make it simpler, let's say I have sheet 1 and 2. On sheet 2, I have
> data from cells b9 to c109. I'm trying to use the vlookup to reference
> the data label in the first column of sheet 2 (column b in the array)
> and lookup the data value in the second column (column c).
>
> on the first sheet, i have the second sheet name in cell d3 and the
> lookup reference in cell b3. I think Biff's suggestion may work, but I
> don't know how to manipulate the indirect function. Excel's help tool
> was pretty useless.
>
> Thanks,
> Joe
>

Thanks!