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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Danny Bhoy - 16 Jan 2008 03:39 GMT

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

Reply to this Message

Danny Bhoy - 16 Jan 2008 03:58 GMT

> I've got the standard task of referencing cells in other sheets from a
> summary sheet using vlookup, no problems there.
[quoted text clipped - 11 lines]
>
> 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.

Reply to this Message

papou - 16 Jan 2008 09:21 GMT

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.
[quoted text clipped - 15 lines]
> 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.

Reply to this Message