Vlookup refs a cell that has a worksheet name

[Peter]

How can I get VLOOKUP to look at a cell which contains the name of a
Worksheet, from this, get it to look at a the same named Worksheet and do
its "looking".
The aim is to avoid creating individual 100 VLOOKUPS that each need to look
at a unique worksheet (the base worksheet already has 100 worksheet names
typed into col A). This would then sum up common criteria (numbers) found in
those worksheets?

This is what I've tried, unsuccessfully: =
VLOOKUP(A6,INDIRECT("A5")$B$6:$C$10,2,FALSE).

I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
worksheet, based on looking at a worksheet name in cell B1, but I can not
get this function to join up with VLOOKUP.


Thanks

Peter

 

[Guest]

Just looking at your question, I don't believe there is a worksheet function
that can accomplish what you want without some programming. So in your case,
there are many ways to go about how to do this. Since there are 100 lookups
to be done, you will most definitely need a Loop of some sort. You should
post this in the Programming Section.

 

[Guest]

Try this one:

=VLOOKUP(A6,INDIRECT((A5) &"!$B$6:$C$10"),2,FALSE)

 

[Roger Govier]

Hi Peter

Whilst you could achieve a solution with Vlookup and Indirect, I believe
that a simpler approach would be more efficient.
On each sheet, do whatever formula is necessary to get you result to a
single common location e.g. X1.
It may be that you don't require a formula, it is the value in that
location that you want..

Then, create 2 new sheets and label them First and Last.
Have your summary Sheet, then First, then all of your 100 Sheets then
Last. First and Last can be hidden if required.
On your Summary Sheet, just enter
=SUM(First:Last!X1)

 

[Peter]

Thanks for looking Kou

Just looking at your question, I don't believe there is a worksheet
function
that can accomplish what you want without some programming. So in your
case,
there are many ways to go about how to do this. Since there are 100
lookups
to be done, you will most definitely need a Loop of some sort. You should
post this in the Programming Section.

 

[Peter]

Your solution worked excellent for names without spaces inbetween, any ideas
on how to extend it to include names with spaces, eg "Normans Spices"?
I know that a straight forward VLOOKUP needs an apostrophe before and after
a two-part worksheet name search.

Thanks for your help.
Peter

 

[Peo Sjoblom]

=VLOOKUP(A6,INDIRECT("'"&A5&"'!$B$6:$C$10"),2,0)

note that the added apostrophes will work for all types of sheet names so
you don't need to use 2 formulas (one for spaces and one for non spaces)

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Peter]

Tried it today and it works great!!!

Very much appreciated all.

Peter