Biff

thank you for taking the time to write out the explanation of the
formula, some of it made sense to my limited knowledge.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

I input the above formula as an array and excel offered to correct it
as below

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )*0,0))&"'!A:Z"),15,0)

adding in the * near the add. Having accepted this the formula returns
#N/A
even though I have added data into the worksheets and asked to look up
something I know is there.

I know #N/A means that the formula cannot find the referenced data,
does this mean that I have entered the names of the monthly worksheets
wrong
(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is
there another way of entering the names of the worksheets?

Sorry about this but it is so frustrating as it appears so close to
completion

thanks again for any help

stm
Biff Wrote:
Hi!
-
I cant follow the formula and was wondering if you could explain each
bit
this appears far more advanced than anything I've done before.-

Ok, if I do a good job of explaining what the formula does after you
read it
you'll be thinking to yourself, Man, that's really simple after all!

I'll change the formula a little using the info and references you
provided
in your response.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

Since you have 12 sheets for monthly data I'll take a wild guess and
assume
that the names a

Jan
Feb
Mar
Apr
May
...
...
Dec

I think Domenic (knows his stuff!) probably figured out why the
formula
didn't work initally. Since one of your goals was to reduce the number
of
sheets in the workbook why create a sheet just to list the sheet names
to be
used for this formula? Just put the list somewhere on your summary
sheet.
You can put the list anywhere you want if you don't want it to be
visible on
screen.

OK, the sheet names are listed in the range H1:H12 (on the summary
sheet).
Select that range, H1:H12. Now, click in the Name box and type in the
name
for that range, SheetList. The Name box is that little space on the far
left
hand side of the formula bar. It shows you what cell is currently
selected.

Now, since you want to do a lookup on several sheets (12) you would
think
that that is what the formula is doing. Sort of a looping lookup. But
that's
NOT what the formula is doing. It's doing a single lookup but part of
the
formula IS "looping" looking for a condition associated with the
lookup
value.

If you only had 2 or 3 sheets then you could use a "looping" Vlookup
formula. That is, a Vlookup formula that searches one sheet then the
next,
then the next. That formula might look something like this:

=IF(NOT(ISERROR(VLOOKUP(C10,Sheet2!A:Z,15,0))),VLO OKUP(C10,Sheet2!A:Z,15,0),
IF(NOT(ISERROR(VLOOKUP(C10,Sheet3!A:Z,15,0))),VLOO KUP(C10,Sheet3!A:Z,15,0),
IF(NOT(ISERROR(VLOOKUP(C10,Sheet4!A:Z,15,0))),VLOO KUP(C10,Sheet4!A:Z,15,0),
"")))

The formula looks through sheet2, if the lookup value isn't found then
it
looks through Sheet3, if the lookup value isn't found then it looks
through
Sheet4. So it "loops" through the sheets.

Since you have 12 sheets to look through this type of formula won't
work
because you would exceed the nested functions limit of 7. (although
technically, you could use a concatenation technique to get around the
nested function limit but then the formula would be REALLY long!)

So, this formula:

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

does a single lookup.

=VLOOKUP(C10,

That portion should be self explanatory. Now, we have to tell Vlookup
where
to look.

The Indirect function is used to "build" a TEXT representation of a
reference that can be converted into a useable reference by Vlookup.

Assume the lookup value is the number 10. This portion of the formula:

COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)

is "looking" on each sheet for that value. Remember now, this is an
array
formula. An array formula operates on each element of the array. So,
each
sheet is an element of the array. The formula operates on each element
by
doing a Countif. Here's what that would look like:

COUNTIF(Jan!A2:A999,C10)0
COUNTIF(Feb!A2:A999,C10)0
COUNTIF(Mar!A2:A999,C10)0
etc.

What that's doing is if the lookup value 10 is found in sheet
Jan!A2:A999
then the logical Countif( ) 0 will return TRUE. If the lookup value is
not
found in Jan!A2:A999 then the logical Countif( ) 0 will return FASE.
And
this is performed on each element of the array.

This portion:

MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A9 99"),C10)0,0))

looks for a return of TRUE meaning the Countif of the lookup value is
greater than zero. Assume the sheet Mar!A2:A999 is where the lookup
value
was "found". The formula would return this array based on the results
of
each Countif:

FALSE
FALSE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE


Notice that the TRUE is in the third position. The Index function is
used to
reference an array of values one of which may or may not be one that we
want
to return in a formula. To determin which of those values we want
returned
we use conditions that must be met. Our conditions that need to be met
a

MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A9 99"),C10)0,0))

The array of values that we have indexed using the Index function are
the
sheet names:

INDEX(SheetList

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sept
Oct
Nov
Dec

So, this potion of the formula:

INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sh eetList&"'!A2:A999"),C10)0,0))

looks like this:

Jan FALSE
Feb FALSE
Mar TRUE
Apr FALSE
May FALSE
Jun FALSE
Jul FALSE
Aug FALSE
Sept FALSE
Oct FALSE
Nov FALSE
Dec FALSE

This means that the lookup value 10, is on sheet Mar.

So, now the formula looks like this:

=VLOOKUP(C10,INDIRECT("'Mar'!A:Z"),15,0)

Indirect "converts" the TEXT string "'Mar'!A:Z" to a useable reference
and
we end up with:

=VLOOKUP(C10,Mar!A:Z,15,0)

See, it really isn't complicated at all! g

I left a lot of explanation out but covered the basics of the formula.
Hope
you get something out of it!

P.S. - the formula does not contain an error trap. That is, if the
lookup
value is not found the formula will return #N/A. You can suppress the
display of #N/A if you don't want it showing up on your sheet. There
are 2
methods for doing this. One is building the error trap into the formula
but
this will make the formula twice as long. the other method is to use
conditional formatting. If you want to do this post back and "we'll"
fix ya
right up!

Oh, and don't ask about finding the second or third or fourth instance
of
the lookup value! vbg

Biff

"sonic-the-mouse"
wrote
in message ...-

Hi!

This is way above anything I have done before. I entered the formula
as
is and remembered CTRL SHFT ENTER, came back with automatic
correction
of adding an * ",A1)*0,0))etc. This just returns #N/A.

OK, this will work if the lookup tables on all the sheets have the
same
layout. -ALL WORKSHEETS FOR THE MONTHS ARE THE SAME LAYOUT

Make a list of sheet names that need to be "searched". Say you put
that
list
in H1:H12. Give that range a name, something like SheetList. -ENTERED
A
NEW WORK SHEET NAMED SHEETLIST AND TYPED IN NAMES OF WORKSHEETS INTO
COLUMN A1:A12

The lookup value is entered in A1. - IN MY WORKSHEET C10

The lookup tables on all the sheets are in the range A1:C10. - IN MY
CASE A2:Z999

This example looks up the value in column A and returns the
corresponding
value from column 3 of the lookup table.


=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)0, 0))&"'!A:C"),3,0)

I cant follow the formula and was wondering if you could explain each
bit

Formula goes into cell G8 which looks for the info in C10 in all the
work books and then returns the information in the same row but in
Column O.

Sorry if I appear to be a bit thick but this appears far more
advanced
than anything I've done before.

stm


--
sonic-the-mouse-


--
sonic-the-mouse

Hi!

This is the correct formula:

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

The version you keep posting:

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

is missing the greater than logical operator and that's why Excel see's it
as an error and wants to correct it. Here's what's really strange, I'm
replying to your post and in the previous quoted response from me the
formula is also missing the greater than logical operator. But in my reply
that is the explanation of the formula, the operator is there.

Let me try this. The portion of the formula that Excel wants to correct
should look like this:

A2:A999"),C10)0,0))&"'!A:Z"),15,0)

After C10) and before 0 there should be the greater than operator .

I know #N/A means that the formula cannot find the referenced data,
does this mean that I have entered the names of the monthly worksheets
wrong
(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is
there another way of entering the names of the worksheets?

You shouldn't have to do anything to the sheet names. The single quotes and
exclaimation mark are used in a formula to let Excel know that refers to a
different worksheet.

If you still have problems I can put together a sample file that
demonstrates this technique. If you would be interested in that, post an
email address and I'll contact you.

Biff

"sonic-the-mouse" wrote
in message ...

Biff

thank you for taking the time to write out the explanation of the
formula, some of it made sense to my limited knowledge.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

I input the above formula as an array and excel offered to correct it
as below

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )*0,0))&"'!A:Z"),15,0)

adding in the * near the add. Having accepted this the formula returns
#N/A
even though I have added data into the worksheets and asked to look up
something I know is there.

I know #N/A means that the formula cannot find the referenced data,
does this mean that I have entered the names of the monthly worksheets
wrong
(I tried entering them, by typing, as 'Jan'!, 'Feb'!, 'Mar'! etc). Is
there another way of entering the names of the worksheets?

Sorry about this but it is so frustrating as it appears so close to
completion

thanks again for any help

stm
Biff Wrote:
Hi!
-
I cant follow the formula and was wondering if you could explain each
bit
this appears far more advanced than anything I've done before.-

Ok, if I do a good job of explaining what the formula does after you
read it
you'll be thinking to yourself, Man, that's really simple after all!

I'll change the formula a little using the info and references you
provided
in your response.

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

Since you have 12 sheets for monthly data I'll take a wild guess and
assume
that the names a

Jan
Feb
Mar
Apr
May
...
...
Dec

I think Domenic (knows his stuff!) probably figured out why the
formula
didn't work initally. Since one of your goals was to reduce the number
of
sheets in the workbook why create a sheet just to list the sheet names
to be
used for this formula? Just put the list somewhere on your summary
sheet.
You can put the list anywhere you want if you don't want it to be
visible on
screen.

OK, the sheet names are listed in the range H1:H12 (on the summary
sheet).
Select that range, H1:H12. Now, click in the Name box and type in the
name
for that range, SheetList. The Name box is that little space on the far
left
hand side of the formula bar. It shows you what cell is currently
selected.

Now, since you want to do a lookup on several sheets (12) you would
think
that that is what the formula is doing. Sort of a looping lookup. But
that's
NOT what the formula is doing. It's doing a single lookup but part of
the
formula IS "looping" looking for a condition associated with the
lookup
value.

If you only had 2 or 3 sheets then you could use a "looping" Vlookup
formula. That is, a Vlookup formula that searches one sheet then the
next,
then the next. That formula might look something like this:

=IF(NOT(ISERROR(VLOOKUP(C10,Sheet2!A:Z,15,0))),VLO OKUP(C10,Sheet2!A:Z,15,0),
IF(NOT(ISERROR(VLOOKUP(C10,Sheet3!A:Z,15,0))),VLOO KUP(C10,Sheet3!A:Z,15,0),
IF(NOT(ISERROR(VLOOKUP(C10,Sheet4!A:Z,15,0))),VLOO KUP(C10,Sheet4!A:Z,15,0),
"")))

The formula looks through sheet2, if the lookup value isn't found then
it
looks through Sheet3, if the lookup value isn't found then it looks
through
Sheet4. So it "loops" through the sheets.

Since you have 12 sheets to look through this type of formula won't
work
because you would exceed the nested functions limit of 7. (although
technically, you could use a concatenation technique to get around the
nested function limit but then the formula would be REALLY long!)

So, this formula:

=VLOOKUP(C10,INDIRECT("'"&INDEX(SheetList,MATCH(TR UE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10 )0,0))&"'!A:Z"),15,0)

does a single lookup.

=VLOOKUP(C10,

That portion should be self explanatory. Now, we have to tell Vlookup
where
to look.

The Indirect function is used to "build" a TEXT representation of a
reference that can be converted into a useable reference by Vlookup.

Assume the lookup value is the number 10. This portion of the formula:

COUNTIF(INDIRECT("'"&SheetList&"'!A2:A999"),C10)

is "looking" on each sheet for that value. Remember now, this is an
array
formula. An array formula operates on each element of the array. So,
each
sheet is an element of the array. The formula operates on each element
by
doing a Countif. Here's what that would look like:

COUNTIF(Jan!A2:A999,C10)0
COUNTIF(Feb!A2:A999,C10)0
COUNTIF(Mar!A2:A999,C10)0
etc.

What that's doing is if the lookup value 10 is found in sheet
Jan!A2:A999
then the logical Countif( ) 0 will return TRUE. If the lookup value is
not
found in Jan!A2:A999 then the logical Countif( ) 0 will return FASE.
And
this is performed on each element of the array.

This portion:

MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A9 99"),C10)0,0))

looks for a return of TRUE meaning the Countif of the lookup value is
greater than zero. Assume the sheet Mar!A2:A999 is where the lookup
value
was "found". The formula would return this array based on the results
of
each Countif:

FALSE
FALSE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE


Notice that the TRUE is in the third position. The Index function is
used to
reference an array of values one of which may or may not be one that we
want
to return in a formula. To determin which of those values we want
returned
we use conditions that must be met. Our conditions that need to be met
a

MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A9 99"),C10)0,0))

The array of values that we have indexed using the Index function are
the
sheet names:

INDEX(SheetList

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sept
Oct
Nov
Dec

So, this potion of the formula:

INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&Sh eetList&"'!A2:A999"),C10)0,0))

looks like this:

Jan FALSE
Feb FALSE
Mar TRUE
Apr FALSE
May FALSE
Jun FALSE
Jul FALSE
Aug FALSE
Sept FALSE
Oct FALSE
Nov FALSE
Dec FALSE

This means that the lookup value 10, is on sheet Mar.

So, now the formula looks like this:

=VLOOKUP(C10,INDIRECT("'Mar'!A:Z"),15,0)

Indirect "converts" the TEXT string "'Mar'!A:Z" to a useable reference
and
we end up with:

=VLOOKUP(C10,Mar!A:Z,15,0)

See, it really isn't complicated at all! g

I left a lot of explanation out but covered the basics of the formula.
Hope
you get something out of it!

P.S. - the formula does not contain an error trap. That is, if the
lookup
value is not found the formula will return #N/A. You can suppress the
display of #N/A if you don't want it showing up on your sheet. There
are 2
methods for doing this. One is building the error trap into the formula
but
this will make the formula twice as long. the other method is to use
conditional formatting. If you want to do this post back and "we'll"
fix ya
right up!

Oh, and don't ask about finding the second or third or fourth instance
of
the lookup value! vbg

Biff

"sonic-the-mouse"
wrote
in message ...-

Hi!

This is way above anything I have done before. I entered the formula
as
is and remembered CTRL SHFT ENTER, came back with automatic
correction
of adding an * ",A1)*0,0))etc. This just returns #N/A.

OK, this will work if the lookup tables on all the sheets have the
same
layout. -ALL WORKSHEETS FOR THE MONTHS ARE THE SAME LAYOUT

Make a list of sheet names that need to be "searched". Say you put
that
list
in H1:H12. Give that range a name, something like SheetList. -ENTERED
A
NEW WORK SHEET NAMED SHEETLIST AND TYPED IN NAMES OF WORKSHEETS INTO
COLUMN A1:A12

The lookup value is entered in A1. - IN MY WORKSHEET C10

The lookup tables on all the sheets are in the range A1:C10. - IN MY
CASE A2:Z999

This example looks up the value in column A and returns the
corresponding
value from column 3 of the lookup table.


=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRU E,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)0, 0))&"'!A:C"),3,0)

I cant follow the formula and was wondering if you could explain each
bit

Formula goes into cell G8 which looks for the info in C10 in all the
work books and then returns the information in the same row but in
Column O.

Sorry if I appear to be a bit thick but this appears far more
advanced
than anything I've done before.

stm


--
sonic-the-mouse-


--
sonic-the-mouse

In article ,
"Biff" wrote:

Here's what's really strange, I'm
replying to your post and in the previous quoted response from me the
formula is also missing the greater than logical operator. But in my reply
that is the explanation of the formula, the operator is there.

We must be in the 'twilight zone'. :)