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.