HELP - Vlookup with referenced tab name

N

nxqviet

Hi all,

I have a question regarding Vlookup, here is the code

=VLOOKUP($B111,('[MainFile.xls]Sheet1'!$A$8:$D$150),3,0)

I have about 30 sheets, and I want to replace the "Sheet1" in that
formula with a cell reference, which correspond to the heading of each
column. For example, Column C5 contains Sheet1, D5 contains Sheet2, D6
= Sheet3...So that when I copy the fomula accross the sheet, the
formular will change accordingly.

I know the following formular is wrong, can some one help please. As
you can see, all I want is to replace "Sheet1" with a cell reference.

=VLOOKUP($B111,('[MainFile.xls] & C$5 & '!$A$8:$D$150),3,0)

Thanks,


V_
 
T

Tim Williams

Try:

=VLOOKUP($B111,INDIRECT('[MainFile.xls] & C$5 & '!$A$8:$D$150),3,0)

Tim
 
N

nxqviet

This Code works Perfectly!!!!! Thank you very much for your help.


V_





Try:

=VLOOKUP($B111,INDIRECT('[MainFile.xls] & C$5 & '!$A$8:$D$150),3,0)

Tim




I have a question regarding Vlookup, here is the code
=VLOOKUP($B111,('[MainFile.xls]Sheet1'!$A$8:$D$150),3,0)

I have about 30 sheets, and I want to replace the "Sheet1" in that
formula with a cell reference, which correspond to the heading of each
column. For example, Column C5 contains Sheet1, D5 contains Sheet2, D6
= Sheet3...So that when I copy the fomula accross the sheet, the
formular will change accordingly.
I know the following formular is wrong, can some one help please. As
you can see, all I want is to replace "Sheet1" with a cell reference.
=VLOOKUP($B111,('[MainFile.xls] & C$5 & '!$A$8:$D$150),3,0)

V_- Hide quoted text -

- Show quoted text -
 

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