Mar 18 2024 03:08 PM
Hi all,
I am trying to compare a column which is an index match returned value to a static value cell. And highlight any variances in the index match column if different.
=[@[Updated Department]]<>[@Department] - i have used this in the table to identify true/false. However when using conditional formatting, i cannot seem to get this to operate and highlight. How would i go about completing this to highlight?
Mar 18 2024 03:23 PM
You cannot use structured table references in a conditional formatting formula (at least not directly).
Use the cell references instead, referring to the first data row of the table.
Mar 18 2024 03:33 PM
Mar 18 2024 03:39 PM
Mar 18 2024 03:51 PM
Hi Hans,
I am still facing some difficulties, i have included a test document which fairly represents what im trying to do, as you can see the conditional format should reference row to row, and only highlight where the value varies, in the example it is highlighting an example where there is no change.
Mar 18 2024 03:57 PM
You applied the rule to the entire column B.
You should apply it to $B$2:$B5 instead. Since that is a table column, Excel will automatically expand it when you add new rows to the table.
See the attached version.
Mar 18 2024 04:04 PM
Mar 18 2024 05:06 PM
You can use the structured references if they are 'hidden' within a defined name.
= (updated = original)
"where"
updated = Table1[@updated]
original = Table1[@original]
It gets rid of direct cell referencing with the mixed $ anchoring but whether that is worth the effort is a matter of opinion.