Vlookup

[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!

 

[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)

 

[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

 

[Guest]

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

why is not working? and thank you again!

Leo

 

[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





.

 

[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)

 

[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)




.