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)
--
Don Guillett
SalesAid Software

"scott23" wrote in message
om...
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