I have several versions of this issue but the attached spreadsheet is a simple one that shows the problem. The spreadsheet lists sales, sorted by vendor. The conditional formatting draws a line under the last sale for a given vendor. If you add a row to the table and resort, the conditional formatting breaks. Excel seems to add at least two new criteria to the list rather than just extend the original criteria. One of the new ones has a #Ref error and the other one seems to be only for one row, but it's not really correct.
Any idea how to fix this? It's getting old editing the conditional formatting every time new data shows up.
Thanks,
Larry
Hi Larry,
No file attached.
Click on the Start Upload button after selecting the file.
Regards
Phil
I thought I did that but I'll try again. I see the file in the Attachments section but when I click "View existing file uploads" I don't see it.
Difficult to explain but working with formulas like =B2<>B3 in a CF rule doesn't work as you might think. If you can live with alternating color bands per vendor, the attached file does just that and Excel doesn't get confused as long as you sort and re-sort column B. Adding new rows to the table automatically extends the applied range for the CF rule.
The CF rule used here is:
=MOD(SUM(IF(FREQUENCY($B$2:$B2,$B$2:$B2),1)),2)
A bit more complicated than yours. What it does is count the number of times a vendor ID occurs from the first row in the table to the current one. If that number is odd (i.e. MOD(number, 2) equals 1) then the row is colored. Otherwise not.
