comparing multiple ranges

[Guest]

There are 35 sheets in my workbook. I want to know whether the values (in
this case text) are the same in all sheets for a certain range (in this case
A1..A51). I don't care if I know the answer for the range or if I know for
each cell in the range (i.e., A1 True, A2 True...)

 

[Biff]

Hi!

This might work.

As I understand, you want to know if the value in A1 is also entered in a
range on 34 other sheets.

List the 35 sheet names in a range somewhere, say J1:J35.

Now, if the value in A1 is on all 35 sheets then a simple Countif will
return 35.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&J$1:J$35&"'!A1:A51"),A1))=35

Will return TRUE or FALSE.

Copy down as needed.

If you used the default sheet names: Sheet1, Sheet2, Sheet3 etc., then you
could avoid making a list of the names and build the sheet name reference
directly into the formula.

Biff