Simple fomula to compare a portion of a cell's contents to the cell above it / Conditional Formatt.

[CLoos]

I have this formula to compare a portion of a cell's contents to the cell above it. If it is true then I want the formatting to turn a color. I checked this formula in the worksheet and it does produce the correct answer. But when I put it in conditional formatting I get two things happening:

1. It does not use the cell reference correctly as I paste the formatting down
2. It does not color the cell if the formula is TRUE

Can anyone explain this to me?

=IF(TRIM(LEFT(D9,LEN(D9)-4))=TRIM(LEFT(D10,LEN(D10)-4)),D9,D9)

 

[GraH - Guido]

Hi CLoos, always best to upload a file with the example to avoid we need to ask some questions in return.
Which part of the string you want to match? Why is there a minus 4 at both ends of the equation? ...
Did you check your formula in CF after you pasted down? Did D9 change in something like X1048576? ...

Now I notice you are using relative reference and you say you paste down the format. That's a potential combination from hell, and probably the reason why the CF is not working. Also the IF does not serve a purpose in this CF. Like you said, CF works when the formula returns TRUE. You can do without that if. TRIM also looks a bit redundant. But then again, I don't see your data.

If your range on which you want to apply the CF is from D9 until D20, then try this (as in the attached file):

1. select D9 and add a new rule with CF
2. use this formula =LEFT($D8,LEN($D8)-4)=LEFT($D9,LEN($D9)-4)
3. select your colour and confirm
4. in the applies to range set =$D$9:$D$20

 

[CLoos]

Thank you