conditional formatting problem (when pulling source data to new cells)

Hello,

I have a large spreadsheet that contains several tables that are conditionally formatted with formulas (four CF formulas per cell). The CF works great until I make a change to a control data source that is referenced in the CF formulas.

I created a sample of my issue in the attached workbook. In the sample, there is a control sheet which determines how the CF formulas perform (e.g., they get the multi-colored scheme, or they have a grey background). If a cell on the "Control" sheet has a "1", then the CF formulas tell that corresponding cell to be grey on the "Comparison" sheet. This works fine. However, if I pull products B thru E to the right by one column on the "Control" sheet (e.g., product B now shows in column E), the conditional formatting on the "Comparison" sheet stops working for products B thru E (see this by turning off the conditional formatting with the drop-down option after pulling the cells to the right on the Control sheet).

I suspect this has something to do with the absolute references in the CF formulas, but I want to ask the forum. I tried removing the absolute references; however, they automatically re-appear when I apply the changes.

My real workbook has many tables, and changes to the real control sheet are inevitable.

Thanks very much for any insights!

Have you tried copying and paste special - values to move your data over?