Conditional Formatting to compare cells in two different tabs where one tab the information changes location

[Mldeuser]

Hello

I have a workbook with information on two tabs, that I would like to create a conditional format rule to highlight the cell in the first tab if the value is different in the other tab.

The first tab is "Accrual" the cell that would have the rule in it is in column Q (there are several cells that would have this rule)

The second tab is "Utility" the cells that would be compared to the first tab are in column T. Each month we overwrite the information on this tab, so the cell the first tab is comparing to on the second tab could change.

Both tabs column A has a unique identifier to help locate information for other columns, I am just not sure how or if this can be used in a conditional format rule.

Thank you
Mark

 

[Special-K99]

Some older versions of Excel won't allow Conditional Formatting that references a cell on another tab.
The workaround is to define the other tab range as a Name.

So you want to match on the value in A and compare Accrual! column Q against Utility! column T

Try this

Select the range to format (Accrual! column Q)

Conditional Formatting
New Rule
Use a formula to determine...

=(INDEX(Accrual!Q$1:Q$1000,MATCH(A1,Utility!A$1:A$1000,0),1)<>INDEX(Utility!T$1:T$1000,MATCH(A1,Utility!A$1:A$1000,0),1))

Format as required

I think this should work, just wondering what happens if the MATCH fails, it the value in Accrual!A1 does not appear in Utility!A1:A1000

 

[Mldeuser]

Hello

Thank you for the response. I am getting an error message "You may not reference operators (such as unions, intersections, and ranges) or array constants for Conditional Formatting criteria".

 

[Special-K99]

I dont get that error when I enter it on a blank spreadsheet with two tabs named Accrual and Utility.
Just entered some test data, it worked fine.

What version of Excel are you using?