Forum Discussion
Table conditional formatting
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?
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.
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.
- Hi Hans,
Thanks for the reply, i have attempted using cell references, and it does not appear to work. I've tried =$G$2<>$L$2.
i assume the error i am making is straight forward, but whichever way i have attempted to reference the cells, i either get no formatting, or it formats the entire range even where the value isn't true (as if it's referencing only one value).
- A bit late to the discussion, but another way around this would be to wrap the structured references in the INDIRECT function when making use of a formula to determine the conditional formatting.
E.g. INDIRECT("[@[Updated Department]]")<>INDIRECT("[@Department]")- I would guess this to be the best long lasting . I might be wrong but If you use cell reference, you need to tweek it if you add or remove rows. On the other hand that is also true if you change column name of the table when using indirect.
Defined names that refer to structured references will stay in sync. as the table changes as well as being more acceptable to various antiquated corners of Excel such as conditional formatting.
