Formula checking multiple worksheets

I have been tasked with streamlining a spreadsheet used at work. It previously had 28 worksheets
A sheet for each month of the year to list occurences
An audit sheet for each month and
A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are not consecutive.

I want to only have one audit worksheet which will search the monthly sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find the event number? There are a number of formula in the audit sheet which will read further information in the same row. Any assistance is greatly appreciated.

stm

Hi!

It's not clear what you want.

Do you want to lookup a value that is on each sheet or do you want to lookup
a value that may be on any sheet?

Biff

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

I have been tasked with streamlining a spreadsheet used at work. It
previously had 28 worksheets
A sheet for each month of the year to list occurences
An audit sheet for each month and
A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are not
consecutive.

I want to only have one audit worksheet which will search the monthly
sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find the
event number? There are a number of formula in the audit sheet which
will read further information in the same row. Any assistance is
greatly appreciated.

stm


--
sonic-the-mouse

Sorry Biff for my lack of explanation.

I am trying to look up a number that could be on any one of the twelve worksheets. I hope this clarifies what I'm looking for.

stm

Originally Posted by Biff

Hi!

It's not clear what you want.

Do you want to lookup a value that is on each sheet or do you want to lookup
a value that may be on any sheet?

Biff

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

I have been tasked with streamlining a spreadsheet used at work. It
previously had 28 worksheets
A sheet for each month of the year to list occurences
An audit sheet for each month and
A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are not
consecutive.

I want to only have one audit worksheet which will search the monthly
sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find the
event number? There are a number of formula in the audit sheet which
will read further information in the same row. Any assistance is
greatly appreciated.

stm


--
sonic-the-mouse

Hi!

OK, this will work if the lookup tables on all the sheets have 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.

The lookup value is entered in A1.

The lookup tables on all the sheets are in the range A1:C10.

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

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

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

Biff

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

Sorry Biff for my lack of explanation.

I am trying to look up a number that could be on any one of the twelve
worksheets. I hope this clarifies what I'm looking for.

stm
Biff Wrote:
Hi!

It's not clear what you want.

Do you want to lookup a value that is on each sheet or do you want to
lookup
a value that may be on any sheet?

Biff

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

I have been tasked with streamlining a spreadsheet used at work. It
previously had 28 worksheets
A sheet for each month of the year to list occurences
An audit sheet for each month and
A couple of statistc sheets.

The monthly sheets list events numerically although the numbers are
not
consecutive.

I want to only have one audit worksheet which will search the monthly
sheets looking for the event that is numbered in cell C10.

The previous formula was

=if(isblank(c10),"",vlookup('Jan'!a2:t999,2,false) ).

How can I adapt this to look up all the monthly worksheets to find
the
event number? There are a number of formula in the audit sheet which
will read further information in the same row. Any assistance is
greatly appreciated.

stm


--
sonic-the-mouse-


--
sonic-the-mouse

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

The formula that Biff gave you is correct. I think the problem is
here...

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

Once you've typed your list of sheet names, you'll need to define a name
for the range...

Insert Name Define

Name: SheetList

Refers to: SheetList!$A$1:$A$12 (Note here that SheetList refers to
the name of the worksheet you entered your list of sheet names.)

Click Ok

Of course, your list of sheet names doesn't have to be on a separate
sheet.

Hope this helps!

In article ,
sonic-the-mouse
wrote:

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("'"&Sheet
List&"'!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

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

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

Originally Posted by Biff

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

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