LOOKUP across Multiple Sheets

[Guest]

Hi,

I have a single (large) workbook, with multiple worksheets (all same layout
and format).

These multiple sheets show data being returned from multiple sources. I have
to cross-check another sheet of unique reference numbers to see if they exist
in any of the multiple sheets.

I've been using the VLOOKUP function with success, but to do this I have
been consolidating (Using cut & paste) the multiple sheets into one. The
multiples are getting larger and larger (over 15 sheets) now. Is there a way
of searching for a cells contents (lookup_value) across multiple data sources
(Table_array across multiple sheets) and returning a result (or even a #N/A -
at least I'd know if the unique ref exists!).

If there's another function other than VLOOKUP then I'm happy to adapt!

L.

 

[Arvi Laanemets]

Hi

Redesign your workbook. There is no way easily automate such design.

How exactly depends on way your data are returned from sources. A possible
solution:

You can determine a possible max number of rows returned for every source.
You get all data to a single table (through links or queries), preserving
for every source max possible number of rows - so that you get a table with
empty rows between different sources. Now you use an ODBC query to
consolidate this table on separate sheet - without empty rows. After that
you hide the 1st sheet.

 

[Guest]

one way to determine if the reference number is there is to use countifs
=countif('Sheet1'!A:A,ref1)+countif('Sheet2'!A:A,ref1) +...
a value above 0 indicates a match

you can make more complicated functions to indicate which sheet it is
=if(countif('Sheet1'!A:A,ref1)>0,"Sheet1"&"
","")&if(countif('Sheet2'!A:A,ref1)>0,"Sheet2"&" ","")&...

by adding match() you can indicate which row on which sheet
=if(countif('Sheet1'!A:A,ref1)>0,"Sheet1"&" "&match(ref1,'Sheet1'!A:A,0)&"
","")&...

 

[Guest]

Try this:
assume your data in column A of all 15 sheets
A1 is your criteria

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(INDIRECT("1:15"))&"!A:A"),A1))

 

[Guest]

Thanks for your reply. I think the direction I was going is the right way.
I've thought more about the task, and as yet I don't know where the reference
I looking for appears on more than one sheet - if that happens my vlookup
idea falls apart. I think it's best to consolidate using your idea, and then
search. Thanks.

L.