Show matching data in several worksheets

[Steve]

If I have a workbook with 7 sheets in it, e.g.
violet,indigo,blue,green,yellow,orange and red.
And data is in the A column of each sheet.
E.g. violet
1
2
3
4

blue
3
green
2
3

On an 8th sheet, I'd like some sort of table that will show when there is
matching data on the different sheets, something like this:

sheet names violet blue green
violet match in blue match in green

blue match in violet match in green
green match in violet match in blue

Thanks,

Steve

 

[Max]

One way which should deliver it ..
In the 8th sheet,
you have sheetnames listed in A2 down, eg: violet
and listed in B1 across, eg: blue, green
In B2, normal ENTER to confirm
=IF($A2=B$1,"",IF(SUM(INDEX(COUNTIF(INDIRECT("'"&$A2&"'!A2:A1000"),INDIRECT("'"&B$1&"'!A2:A1900")),)),"match in "&B$1,""))
Copy B2 across/fill down to populate. Modify the ranges to suit. Success?
hit the YES below

 

[Max]

In B2, normal ENTER to confirm:

=IF($A2=B$1,"",IF(SUM(INDEX(COUNTIF(INDIRECT("'"&$A2&"'!A2:A1000"),INDIRECT("'"&B$1&"'!A2:A1900")),)),"match in "&B$1,""))

I had meant the expression above to display identical ranges in both sheets
being compared, but inadvertently left it as 1900 in one, and 1000 in the
other. Possibly because I was testing to see whether it works with
non-identical ranges. As a first precaution, do make the ranges identical
when you try it out over there.

 

[Steve]

Thank you. I didn't verify all my data, but what I did check seems to be
working.

One more question: It has to be an exact match for the formula to work ,
doesn't it ? Meaning if "Excel" is on one sheet, and "Excel files" is on
another, it won't produce a match, will it ?

Thanks again,

Steve

 

[Max]

Yes, it uses an exact match