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

Author 
Message 
carift

Posted: 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

Posted: 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

Posted: 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 addin
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.






