Highlight Cells whose Contents Exist Anywhere in the Workbook

[ramaxac]

I have a workbook with separate sheets of data for every work day of the month. The columns of each sheet are the same, so for now I'm using a COUNTIF to highlight cells in a column if their contents exist in the previous day's sheet (ex. in conditional formatting: =COUNTIF('10-25'!$A:$H,A4) applied to $A$4:A$70). I want to expand this formula so that it searches all of the sheets, not only the previous day. Again, the columns are always the same across all of the sheets, so it can always search A:H.

I tried =COUNTIF('9-30:10-25'!$A:$H,A4), but got the error "This type of reference cannot be used in a Conditional Formatting formula."

Thanks in advance for your help!

 

[Fluff]

One option would be to create a list of all the sheets & then make that a named range. you could then use

=SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetList&"'!$A:$H"),A4))

where SheetList is the named range.

 

[etaf]

can you setup a sheet , with all the sheetnames in

then use
something like
=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A30&"'!A:H"), A4))