Conditional formatting and deleting cells

[Threazy]

I have this recurring problem with conditional formatting that I have not been able to figure out. I often use conditional formatting to "line up" data pasted into a spreadsheet from different sources. For example, if I paste a list of record albums from two different sources (say a list of Artist, Album Title, Genre in cols A, B, and C from one source, and Artist, Album Title, Year in cols D, E, and F) I will conditionally format column E (Album 2) for equality to column B (Album Title 1). This works fine the first time I set the conditional formatting for E.

If the data doesn't "line up" (i.e., the Album Titles are not equal), I delete cells or insert cells to move cells up or down to make them "line up". You will no doubt realize that this totally screws up the conditional formatting because the formatting "follows" the cells as they move. I have to remove all the conditional formatting and redo it. Absolute references move, too.

Is there any way around this? Is there any way to tell a conditionally formatted cell "Are you equal to a cell three cells to your left? (even when you move!)") WITHOUT the format trying to maintain a focus on originally referenced cell?

Thanks - Bill

 

[WarPigl3t]

I see no one has responded to you yet and it's probably because of the lack of information provided. I don't know what you are asking. Please provide sample data. Then explain your goal using cell references. I have an idea of what you might be asking for and I feel confident that just about anyone on this forum can answer it given enough information.

 

[Threazy]

Wow - and I was trying to be careful to be clear. So let's go back to my example: six columns: A=Artist, B=Album Title, C=Genre, D=Artist, E =Album Title, F = Year. The data for columns A, B, and C were pasted from one source; D, E, and F from another source. You see in the example, A2-B2 line up with D2 and E2, A3-B3 with D3-E3. If set a conditional formatting for Column E: "=B1" it would highlight E1, E2, and E3 and would not highlight E4 or E5. (continued below)


[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Artist[/TD]
[TD]Title[/TD]
[TD]Genre[/TD]
[TD]Artist[/TD]
[TD]Title[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AC/DC[/TD]
[TD]Back in Black[/TD]
[TD]Rock[/TD]
[TD]AC/DC[/TD]
[TD]Back in Black[/TD]
[TD]1980[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Beatles[/TD]
[TD]Sgt Pepper[/TD]
[TD]Rock[/TD]
[TD]Beatles[/TD]
[TD]Sgt Pepper[/TD]
[TD]1967[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bowie[/TD]
[TD]ZiggyStardust[/TD]
[TD]Rock[/TD]
[TD]Beatles[/TD]
[TD]Abbey Road[/TD]
[TD]1970[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Clapton[/TD]
[TD]Slowhand[/TD]
[TD]Blues[/TD]
[TD]Bowie[/TD]
[TD]Ziggy Stardust[/TD]
[TD]1972[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Clapton[/TD]
[TD]Slowhand[/TD]
[TD]1977[/TD]
[/TR]
</tbody>[/TABLE]


If, however, I inserts cells at A4-C4 and push-down the other cells, Ziggy Stardust would then "line up" with D5-F5, as shown in the following table:


[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Artist[/TD]
[TD]Title[/TD]
[TD]Genre[/TD]
[TD]Artist[/TD]
[TD]Title[/TD]
[TD]Yea[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]AC/DC[/TD]
[TD]Back in Black[/TD]
[TD]Rock[/TD]
[TD]AC/DC[/TD]
[TD]Back in Black[/TD]
[TD]1980[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Beatles[/TD]
[TD]Sgt Pepper[/TD]
[TD]Rock[/TD]
[TD]Beatles[/TD]
[TD]Sgt Pepper[/TD]
[TD]1967[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beatles[/TD]
[TD]Abbey Road[/TD]
[TD]1970[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bowie[/TD]
[TD]Ziggy Stardust[/TD]
[TD]Rock[/TD]
[TD]Bowie[/TD]
[TD]Ziggy Stardust[/TD]
[TD]1972[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Clapton[/TD]
[TD]Slowhand[/TD]
[TD]Blues[/TD]
[TD]Clapton[/TD]
[TD]Slowhand[/TD]
[TD]1977[/TD]
[/TR]
</tbody>[/TABLE]

The problem is that the conditional formatting "follows" the moved cells. In the table just above would NOT conditionally highlight Ziggy Stardust in E5 because the conditional formatting in E5 is still "looking at" the Slowhand value in B6. I want the conditional formatting to KEEP looking at B5 and highlight that the equality is now true. (And E6 would now also be highlighted as true.

Thanks for asking - I hope this example makes it clearer.

 

[WarPigl3t]

I see what you are talking about now. Good explanation. Alright so you shouldn't be adding cells is your problem. If you need to add something in the middle of your table like that, you should add an entire row. Inserting a single cell will mess up your formatting regardless if it is formulas or conditional formatting. Don't do it! Try inserting an entire row and then add sample data. See if you still have that problem.

 

[Threazy]

Thanks, WP - I already knew I could do what you suggested. I was looking for an easier way to line-up rows by inserted/deleting cells and keep the conditional formatting intact as an indicator.

So: I devised a solution that works! I added a column between the "two sides" that I'm comparing - Column C and Col D in the above example. In the first cell of that column I created a formula using the OFFSET function: "=IF(OFFSET(D1,0,-2)=OFFSET(D1,0,2),"X","")" If the titles are equal, the formula inserts an X in the cell - then I use conditional formatting for when the cell value equals X. The beauty is the reference cell value (e.g., the D1) changes correctly when copied down and the OFFSET always maintains it's eye on the cells 2 to the left and 2 to the right.

Not perfect, but it works.

 

[Threazy]

Okay - one better! Using OFFSET, this is how to do conditional formatting to achieve the behavior I was looking for when inserting/deleting cells to line up data without using the surrogate column and "X". In my example tables above, to conditionally highlight the title in Column E if it matches the title in Column B, use: =(E1=OFFSET(E1,0,-3)) -- this will maintain the relative-condition checking even when inserting/deleting cells.