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





PostPosted: Tue Jan 15 21:58:44 CST 2008 Top

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

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

Excel451  
 
 
Danny





PostPosted: Tue Jan 15 21:58:44 CST 2008 Top

Excel Programming >> odd vlookup solution required - can you rise to the challenge?!
> 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





PostPosted: Wed Jan 16 03:21:25 CST 2008 Top

Excel Programming >> odd vlookup solution required - can you rise to the challenge?! 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.