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

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" wrote:

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

Try this one:

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

--
Kevin Vaughn


"Peter" wrote:

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

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)

--
Regards

Roger Govier


"Peter" wrote in message
...
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

Thanks for looking Kou

"Kou Vang" wrote in message
...
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" wrote:

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

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

"Kevin Vaughn" wrote in message
...
Try this one:

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

--
Kevin Vaughn


"Peter" wrote:

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

=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" wrote in message
...
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

"Kevin Vaughn" wrote in message
...
Try this one:

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

--
Kevin Vaughn


"Peter" wrote:

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

Tried it today and it works great!!!

Very much appreciated all.

Peter

"Peo Sjoblom" wrote in message
...
=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" wrote in message
...
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

"Kevin Vaughn" wrote in message
...
Try this one:

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

--
Kevin Vaughn


"Peter" wrote:

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