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.

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"LeeM" wrote in message
...
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.

one way to determine if the reference number is there is to use countifs
=countif('Sheet1'!A:A,ref1)+countif('Sheet2'!A:A,r ef1) +...
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)&"
","")&...

"LeeM" wrote:

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.

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))


"LeeM" wrote:

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.

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.

"Arvi Laanemets" wrote:

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.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"LeeM" wrote in message
...
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.