HELP - Vlookup with referenced tab name

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

 

[Tim Williams]

Try:

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

Tim

 

[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

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_- Hide quoted text -

- Show quoted text -