Conditional Formatting and 'fill down'

[nielf]

I'm trying to apply conditional formatting a number of rows using a formula.

The formula looks like this:

=NOT(ISBLANK(J20)) which applies to cell F20

I now want to fill down this conditional formatting so that =NOT(ISBLANK(J21)) applies to F21 and so on. However, when I try to do that, the formula keeps refering to J20 even though the reference isn't absolute.

I've tried using the Format Painter - and that works for a single cell which is great. However, when I apply it to all the cells at once it keeps refering to the first cell (J20). How can I avoid that?

Screenshots:

 

[XLearner]

Hi,

1. Remove all existing Cond. Format.
2. Select F20 and create your CF: =NOT(ISBLANK(J20))
3. Make sure it applies to =$F$20<strike></strike>
4. Drag down (using the lower right corner square in F20) as necessary i.e. until F27

Now if you select i.e. F21 you're right the CF formula still say =NOT(ISBLANK(J20) but when you have a look to the Applies to field you'll see =$F$20<strike></strike>:$F$27 and that's where the "magic" occurs...

Not sure how to explain this but the fact that the rule applies to $F$20<strike></strike>:$F$27 (so F20, F21, F22...) the NOT(ISBLANK(J20)) "becomes" (and you don't see it) NOT(ISBLANK(J21)), NOT(ISBLANK(J22))... NOT(ISBLANK(J27)) because your original formula isn't absolute with regard to the row number.

Once you have it working go back to F20 and change your formula to =NOT(ISBLANK(J$20). $F$20<strike></strike>:$F$27 (all cells) will then get the CF color if J20 (and only J20) isn't empty. If J20 is empty all cells ($F$20<strike></strike>:$F$27) won't get the CF color.

Let me know if this is still not clear

Regards
XLearner

 

[nielf]

Thanks a lot, XLearner. That was the explanation I was looking for.

 

[jmacleary]

I suspect that the fill down is actually working correctly. Try entering a value in for example j25 and see if the appropriate formatting appears in f25.

This shows the result of putting a red fill in the condition. I put your formula in the conditon for F20, then filled down. Highlighting F25 gives the following in conditional formatting manager:

Excel makes it look as though the formula isnt being treated as relative, but as you can see, it actually is.

 

[nielf]

Yes, exactly. I can now see that the formula is in fact working correctly. However, Excel confused me as it didn't look like it was relative.

Thank you for your replies.

 

[XLearner]

You're welcome & Thanks for posting back