vary the vlookup array depending on the value in a cell

G

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?
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top