vlookup question

[scott23]

I have the following lookup formula:

=VLOOKUP($A$1,ES!$A$4:$S$170,15,FALSE)

where ES! is obviously the name of a worksheet.
Is it possible to modify the formula so that rather than using ES!, it
will reference another cell where the user can input the name of the
worksheet they want to lookup from ?

for example - if i add a cell A2 where the user can type the name of
any of the other 7 worksheets they prefer to lookup from, is it
possible to reference cell A2 as the worksheet name that is in front
of the table range ? I guess its sort of a nested vlookup ?

I tried : vlookup($a$1,(a2)!$A$4:$s$170,15,false)
but it didnt work.

Any help would be greatly appreciated.
scott

 

[Bob Phillips]

Scott,

You need INDIRECT

VLOOKUP($A$1,INDIRECT(A2&"!$A$4:$S$170"),15,FALSE)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

try using INDIRECT which will not work on external workbooks unless open

in c4 is typed ES (watch SPELLING & CASE)

=VLOOKUP($A$1,INDIRECT(C4&"!$a$4:$s$170"),15,0)