odd vlookup solution required - can you rise to the challenge?!

[Danny Bhoy]

I've got the standard task of referencing cells in other sheets from a
summary sheet using vlookup, no problems there.

The problem I have is that the sheet names to be indexed are listed in
a column in my summary sheet. I can't find a way to create the
vlookup formula referencing a cell to indicate the sheet name.

currently it works like this:
=VLOOKUP($C$4,'[BBC123.xls]Sheet1'!$E$16:$K$21,2,FALSE)

I want something like:
(cell C5 = BBC123.xls)
=VLOOKUP($C$4,'[ C5 ]Sheet1'!$E$16:$K$21,2,FALSE)

Ideas most welcome! TIA

 

[Danny Bhoy]

I've got the standard task of referencing cells in other sheets from a
summary sheet using vlookup, no problems there.

The problem I have is that the sheet names to be indexed are listed in
a column in my summary sheet. I can't find a way to create the
vlookup formula referencing a cell to indicate the sheet name.

currently it works like this:
=VLOOKUP($C$4,'[BBC123.xls]Sheet1'!$E$16:$K$21,2,FALSE)

I want something like:
(cell C5 = BBC123.xls)
=VLOOKUP($C$4,'[ C5 ]Sheet1'!$E$16:$K$21,2,FALSE)

Ideas most welcome! TIA

Update: I have been provided with a solution using an INDIRECT however
for it to work the sheet references must be open. I'm working with
100's of sheets so this is not a solution that will work for me.

 

[papou]

Hi
To make INDIRECT work with closed workbooks you need the free add-in
MOREFUNC.XLL from Laurent Longre.
You can download it here:
http://xcell05.free.fr/morefunc/english/index.htm


HTH
Cordially
Pascal

I've got the standard task of referencing cells in other sheets from a
summary sheet using vlookup, no problems there.

The problem I have is that the sheet names to be indexed are listed in
a column in my summary sheet. I can't find a way to create the
vlookup formula referencing a cell to indicate the sheet name.

currently it works like this:
=VLOOKUP($C$4,'[BBC123.xls]Sheet1'!$E$16:$K$21,2,FALSE)

I want something like:
(cell C5 = BBC123.xls)
=VLOOKUP($C$4,'[ C5 ]Sheet1'!$E$16:$K$21,2,FALSE)

Ideas most welcome! TIA

Update: I have been provided with a solution using an INDIRECT however
for it to work the sheet references must be open. I'm working with
100's of sheets so this is not a solution that will work for me.