Forum Discussion
Conditional formatting and Tables
Wyn, it still inserts extra rules if references in rule formula are out of applied range, like in this case - formula uses S4 but range starts from S5.
SergeiBaklan It assure you this is not the case. I just went in and deleted that rule to check and when I inserted a row it did the same thing.
Just in case, could you please share rule formula?
...actually you might be right. I deleted the two formula rules that had to do with matching one row to another. Once I inserted another row that didn't affect any of the other rules in the sheet.
Thank you for the formulas. Instead of $T5<>"" you may use something like =OFFSET($T6,-1,0)<>"". I didn't test but perhaps that could help.
Here's a couple of them, pretty basic rules
=$AA6<>"" applies to =$AA$6:$AA$93
=AND($E6="",$F6="") Applies to =$E$6:$F$93,$U$6:$Y$93
These I used to indicate if two opposing numbers cancel each other out
=AND($T6<>"",$T7<>"",$T6=$T7*-1) Applies to =$T$6:$T$93
=AND($T5<>"",$T6<>"",$T5=$T6*-1) Applies to =$T$6:$T$93
I feel like the issue only has to do with the 'applies to' part and inserting a row into the middle of the table makes excel do a kind of workaround to get it to apply to the whole range. Very strange though