Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
sonic-the-mouse
 
Posts: n/a
Default Formula checking multiple worksheets


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
  #2   Report Post  
Domenic
 
Posts: n/a
Default

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

  #3   Report Post  
Biff
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Print all charts in a workbook (multiple worksheets) aewsaws Charts and Charting in Excel 4 May 12th 23 03:45 AM
how to create a multiple conditional formula Ad Buijs Excel Discussion (Misc queries) 3 April 20th 05 09:41 PM
Extracting data from multiple worksheets into a list mnirula Excel Worksheet Functions 16 February 25th 05 09:52 PM
adding certain cells in multiple worksheets in multiple workbooks Stephen via OfficeKB.com Excel Worksheet Functions 1 February 4th 05 09:31 PM
Excel should let me refer to the same formula on multiple sheets In need of help Excel Worksheet Functions 1 December 16th 04 09:21 PM


All times are GMT +1. The time now is 08:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"