Vlookup

L

Leo

This is a simple vlookup:

=VLOOKUP($B$9,'sheet1'!$A$1004:$N$1145,F$10,0)

can I replace the 'sheet1' part of the formula with a
reference to a cell in order that my vlookup goes to
diferent sheets in my file when I change the value of the
cell?

help!
 
B

Bob Phillips

Hi Leo,

Assuming sheetname is in A1,

=VLOOKUP($B$9,INDIRECT(A1&"!$A$1004:$N$1145"),F$10,0)

--

HTH

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

Peo Sjoblom

Yes,

notice though that if you work with different workbooks they need to be
open..

=VLOOKUP($B$9,INDIRECT("'"&H2&"'!$A$1004:$N$1004"),F$10,0)

where H2 holds your sheet name
 
G

Guest

Bob,
Thank you very much...
I did your instruction but an error appears: #ref!

why is not working? and thank you again!

Leo
 
G

Guest

Peo

Thank you very much!!!!
it works!!!!

Leo
-----Original Message-----
Yes,

notice though that if you work with different workbooks they need to be
open..

=VLOOKUP($B$9,INDIRECT("'"&H2&"'!$A$1004:$N$1004"),F$10,0)

where H2 holds your sheet name



--

Regards,

Peo Sjoblom





.
Click to expand...
 
B

Bob Phillips

Leo,

Could be that the sheet name has embedded spaces. This should overcome that

=VLOOKUP($B$9,INDIRECT("'"&A1&"'!$A$1004:$N$1145"),F$10,0)


--

HTH

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

Guest

Thank you Bob..
it works Perfect!!!!!!

-----Original Message-----
Leo,

Could be that the sheet name has embedded spaces. This should overcome that

=VLOOKUP($B$9,INDIRECT("'"&A1&"'!$A$1004:$N$1145"),F$10,0)


--

HTH

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




.
Click to expand...
 
Top