You could use variance as the consolidation by doing edit replace "sum" by
"var" and then look for non zero values. Another option is to use a 3d
formula:

=MAX(FREQUENCY(Sheet1:Sheet10!A:A,Sheet1:Sheet10!A :A))


"RAYCV" wrote:

Because the Sum function is being used in the Consolidated worksheet, when it
find a record number eg 1234, the total for that is 1234. Therefore, Excel
sees the original record and the total as a duplicate. Hope this makes
sense??

"Ashish Mathur" wrote:

Hi,

I am not quite clear about your comment. May I request you to mail me the
workbook at .

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RAYCV" wrote in message
...
Hi Thanks for this. However it always show that there are duplicates as
the
consolidation displays zeros??

Anyway to omit the zeros in the formula?

"Ashish Mathur" wrote:

Hi,

Unfortunately, Excel cannot lookup in a thee dimensional array. What you
can do here is use the Data Consolidate Function. In a separate sheet,
while you are in cell A1, go to Data Consolidate and in the reference
box,
give the range in sheet 1 and click on Add. Repeat this process of
adding
the range from the nine other worksheets as well. After all the 10
worksheets are added, check the box for 'Create Links to Source Data" and
in
the function drop down, select "Count". Once you click on OK, (from all
the
sheets) will come in this sheet. Now you can use your function.

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RAYCV" wrote in message
...
Hi, I am using the following array formula to check for duplicates
within
a
worksheet.

{=IF(MAX(COUNTIF('Daniel S'!A4:A1000,A4:A1000))1,"Duplicate","No
Duplicates")}

Is there a way that I can extend this so that it checks the same range
across 10 worksheets within a workbook.

Thanks