software

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 quot;lookingquot;.
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(quot;A5quot;)$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

PeterJust 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.

quot;Peterquot; wrote:

gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt; Worksheet, from this, get it to look at a the same named Worksheet and do
gt; its quot;lookingquot;.
gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to look
gt; at a unique worksheet (the base worksheet already has 100 worksheet names
gt; typed into col A). This would then sum up common criteria (numbers) found in
gt; those worksheets?
gt;
gt; This is what I've tried, unsuccessfully: =
gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;
gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt; worksheet, based on looking at a worksheet name in cell B1, but I can not
gt; get this function to join up with VLOOKUP.
gt;
gt;
gt; Thanks
gt;
gt; Peter
gt;
gt;
gt;
gt;
gt;

Try this one:

=VLOOKUP(A6,INDIRECT((A5) amp;quot;!$B$6:$C$10quot;),2,FALSE)

--
Kevin Vaughnquot;Peterquot; wrote:

gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt; Worksheet, from this, get it to look at a the same named Worksheet and do
gt; its quot;lookingquot;.
gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to look
gt; at a unique worksheet (the base worksheet already has 100 worksheet names
gt; typed into col A). This would then sum up common criteria (numbers) found in
gt; those worksheets?
gt;
gt; This is what I've tried, unsuccessfully: =
gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;
gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt; worksheet, based on looking at a worksheet name in cell B1, but I can not
gt; get this function to join up with VLOOKUP.
gt;
gt;
gt; Thanks
gt;
gt; Peter
gt;
gt;
gt;
gt;
gt;

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 Govierquot;Peterquot; gt; wrote in message
...
gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt; Worksheet, from this, get it to look at a the same named Worksheet and
gt; do its quot;lookingquot;.
gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to
gt; look at a unique worksheet (the base worksheet already has 100
gt; worksheet names typed into col A). This would then sum up common
gt; criteria (numbers) found in those worksheets?
gt;
gt; This is what I've tried, unsuccessfully: =
gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;
gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt; worksheet, based on looking at a worksheet name in cell B1, but I can
gt; not get this function to join up with VLOOKUP.
gt;
gt;
gt; Thanks
gt;
gt; Peter
gt;
gt;
gt;
gt;
Thanks for looking Kou

quot;Kou Vangquot; gt; wrote in message
...
gt; Just looking at your question, I don't believe there is a worksheet
gt; function
gt; that can accomplish what you want without some programming. So in your
gt; case,
gt; there are many ways to go about how to do this. Since there are 100
gt; lookups
gt; to be done, you will most definitely need a Loop of some sort. You should
gt; post this in the Programming Section.
gt;
gt; quot;Peterquot; wrote:
gt;
gt;gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt;gt; Worksheet, from this, get it to look at a the same named Worksheet and do
gt;gt; its quot;lookingquot;.
gt;gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to
gt;gt; look
gt;gt; at a unique worksheet (the base worksheet already has 100 worksheet names
gt;gt; typed into col A). This would then sum up common criteria (numbers) found
gt;gt; in
gt;gt; those worksheets?
gt;gt;
gt;gt; This is what I've tried, unsuccessfully: =
gt;gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;gt;
gt;gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt;gt; worksheet, based on looking at a worksheet name in cell B1, but I can not
gt;gt; get this function to join up with VLOOKUP.
gt;gt;
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt; Peter
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
Your solution worked excellent for names without spaces inbetween, any ideas
on how to extend it to include names with spaces, eg quot;Normans Spicesquot;?
I know that a straight forward VLOOKUP needs an apostrophe before and after
a two-part worksheet name search.

Thanks for your help.
Peter

quot;Kevin Vaughnquot; gt; wrote in message
...
gt; Try this one:
gt;
gt; =VLOOKUP(A6,INDIRECT((A5) amp;quot;!$B$6:$C$10quot;),2,FALSE)
gt;
gt; --
gt; Kevin Vaughn
gt;
gt;
gt; quot;Peterquot; wrote:
gt;
gt;gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt;gt; Worksheet, from this, get it to look at a the same named Worksheet and do
gt;gt; its quot;lookingquot;.
gt;gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to
gt;gt; look
gt;gt; at a unique worksheet (the base worksheet already has 100 worksheet names
gt;gt; typed into col A). This would then sum up common criteria (numbers) found
gt;gt; in
gt;gt; those worksheets?
gt;gt;
gt;gt; This is what I've tried, unsuccessfully: =
gt;gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;gt;
gt;gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt;gt; worksheet, based on looking at a worksheet name in cell B1, but I can not
gt;gt; get this function to join up with VLOOKUP.
gt;gt;
gt;gt;
gt;gt; Thanks
gt;gt;
gt;gt; Peter
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
=VLOOKUP(A6,INDIRECT(quot;'quot;amp;A5amp;quot;'!$B$6:$C$10quot;),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

quot;Peterquot; gt; wrote in message
...
gt; Your solution worked excellent for names without spaces inbetween, any
gt; ideas on how to extend it to include names with spaces, eg quot;Normans
gt; Spicesquot;?
gt; I know that a straight forward VLOOKUP needs an apostrophe before and
gt; after a two-part worksheet name search.
gt;
gt; Thanks for your help.
gt; Peter
gt;
gt; quot;Kevin Vaughnquot; gt; wrote in message
gt; ...
gt;gt; Try this one:
gt;gt;
gt;gt; =VLOOKUP(A6,INDIRECT((A5) amp;quot;!$B$6:$C$10quot;),2,FALSE)
gt;gt;
gt;gt; --
gt;gt; Kevin Vaughn
gt;gt;
gt;gt;
gt;gt; quot;Peterquot; wrote:
gt;gt;
gt;gt;gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt;gt;gt; Worksheet, from this, get it to look at a the same named Worksheet and
gt;gt;gt; do
gt;gt;gt; its quot;lookingquot;.
gt;gt;gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to
gt;gt;gt; look
gt;gt;gt; at a unique worksheet (the base worksheet already has 100 worksheet
gt;gt;gt; names
gt;gt;gt; typed into col A). This would then sum up common criteria (numbers)
gt;gt;gt; found in
gt;gt;gt; those worksheets?
gt;gt;gt;
gt;gt;gt; This is what I've tried, unsuccessfully: =
gt;gt;gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;gt;gt;
gt;gt;gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt;gt;gt; worksheet, based on looking at a worksheet name in cell B1, but I can
gt;gt;gt; not
gt;gt;gt; get this function to join up with VLOOKUP.
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Thanks
gt;gt;gt;
gt;gt;gt; Peter
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;
gt;Tried it today and it works great!!!

Very much appreciated all.

Peter

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; =VLOOKUP(A6,INDIRECT(quot;'quot;amp;A5amp;quot;'!$B$6:$C$10quot;),2,0)
gt;
gt; note that the added apostrophes will work for all types of sheet names so
gt; you don't need to use 2 formulas (one for spaces and one for non spaces)
gt;
gt; --
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; Portland, Oregon
gt;
gt;
gt;
gt;
gt; quot;Peterquot; gt; wrote in message
gt; ...
gt;gt; Your solution worked excellent for names without spaces inbetween, any
gt;gt; ideas on how to extend it to include names with spaces, eg quot;Normans
gt;gt; Spicesquot;?
gt;gt; I know that a straight forward VLOOKUP needs an apostrophe before and
gt;gt; after a two-part worksheet name search.
gt;gt;
gt;gt; Thanks for your help.
gt;gt; Peter
gt;gt;
gt;gt; quot;Kevin Vaughnquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Try this one:
gt;gt;gt;
gt;gt;gt; =VLOOKUP(A6,INDIRECT((A5) amp;quot;!$B$6:$C$10quot;),2,FALSE)
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; Kevin Vaughn
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Peterquot; wrote:
gt;gt;gt;
gt;gt;gt;gt; How can I get VLOOKUP to look at a cell which contains the name of a
gt;gt;gt;gt; Worksheet, from this, get it to look at a the same named Worksheet and
gt;gt;gt;gt; do
gt;gt;gt;gt; its quot;lookingquot;.
gt;gt;gt;gt; The aim is to avoid creating individual 100 VLOOKUPS that each need to
gt;gt;gt;gt; look
gt;gt;gt;gt; at a unique worksheet (the base worksheet already has 100 worksheet
gt;gt;gt;gt; names
gt;gt;gt;gt; typed into col A). This would then sum up common criteria (numbers)
gt;gt;gt;gt; found in
gt;gt;gt;gt; those worksheets?
gt;gt;gt;gt;
gt;gt;gt;gt; This is what I've tried, unsuccessfully: =
gt;gt;gt;gt; VLOOKUP(A6,INDIRECT(quot;A5quot;)$B$6:$C$10,2,FALSE).
gt;gt;gt;gt;
gt;gt;gt;gt; I know that =INDIRECT(ADDRESS(6,2,,,B1)) will find info in another
gt;gt;gt;gt; worksheet, based on looking at a worksheet name in cell B1, but I can
gt;gt;gt;gt; not
gt;gt;gt;gt; get this function to join up with VLOOKUP.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; Thanks
gt;gt;gt;gt;
gt;gt;gt;gt; Peter
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;
gt;gt;
gt;