conditional formatting identifying duplicates from another sheet.

Greetings,
I am working with data from 2 sheets. The first sheet keeps weekly data, and the second is a daily update from a csv file. What I am doing is highlighting cells in sheet 1 that have the same identifier as in sheet 2 which is alphanumeric. I am using the following formula in conditional formatting which work for me "=COUNTIF(Sheet2!$A$1:$A$7,A1)>0", but to make life easier im trying to change this to not show filtered data. I have been playing around with AGGREGATE in conditional formatting to change the txt color of the cells not filtered to no avail.

Column A on both sheets is where my identifier is, is there an easier way to identify duplicates on sheet1 from the new data on sheet2 which also incorporates the filtering on sheet2? Also the rows are not the same headers between the sheets other than this field.

I don't have the original document here, but I just created the 2 sheets with minimal data for what im doing. The identifiers are on both sheets, and on sheet 2 I just have a yes or no filter to test with. What I can't figure out is how to get sheet 1 to either not show the conditional formatting if sheet 2 is filtered, or to show a different text color on sheet 1 if sheet 2 is not hidden.

Originally Posted by JeteMc

Hello ArnoldSQ and Welcome to Excel Forum.
This proposed solution employs a helper column (C) on sheet 2.
The formula that populates the helper column is:

Formula:

Please Login or Register  to view this content.

The formula that is used as a conditional formatting rule for column A on sheet 1 is:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Thank you. i was stuck around doing it all in the conditional formatting.