vary the vlookup array depending on the value in a cell

[Greg Bergin]

I want to populate a cell with a value from a pivot table on one of nine
sheets, depending on the value in another cell. I have a list of names in a
column, each of which corresponds to a worksheet within that workbook.
Depending on the name in that column, I want to get data in a pivot table
from their specific worksheet. All the individuals' worksheets are identical
in format, so the only thing that would need to change in a vlookup is the
array. Is there an easy way to do this, rather than having a monster If
formula?

Hi

Sounds like you need the INDIRECT function. Something like:
=VLOOKUP(A2,INDIRECT(A1&"!A2:C100"),2,FALSE)
where your lookup value is in A2, your sheet name is in A1 and your lookup
range on the sheet is A2:C100.

Andy.

"Greg Bergin" <Greg [email protected]> wrote in message
news:[email protected]...
>I want to populate a cell with a value from a pivot table on one of nine
> sheets, depending on the value in another cell. I have a list of names in
> a
> column, each of which corresponds to a worksheet within that workbook.
> Depending on the name in that column, I want to get data in a pivot table
> from their specific worksheet. All the individuals' worksheets are
> identical
> in format, so the only thing that would need to change in a vlookup is the
> array. Is there an easy way to do this, rather than having a monster If
> formula?