vary the vlookup array depending on the value in a cell
 

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?

vary the vlookup array depending on the value in a cell
 

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 wrote in message
...
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?