Copying a Conditional Format to other cells...

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

I have set a formula to calculate how many days between two dates i.e. Date in A5 - date in A4 +1 = number of days in A6. Pretty simple ok.

If there is no info in cells A4 & A5, A6 shows "1".

For cosmetic purposes I inserted a conditional format so that if A4 & A5 are empty then text in A6 is white i.e. it looks blank.

I want to copy this conditional form to other rows but when I do so B6,C6,D6,E6 etc are linked to A4 & A5 and not their counter parts i.e. B,C,D,E4 & 5.

Hope this makes sense!?!

Can anyone tell me how to copy the format from Row A to other Rows?

Thanks very much


1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Dom,

That is because of the $ symbols, they make either column or row absolute.
Try this example for better understanding:
B1: =$A$1
Dragging B1 to the left or the right; the formula remains the same.
B1: =A$1
Dragging B1 to the right will result in =B$1. Column reference is now relative and will change.
Dragging B1 down will result in =A$1. Row reference is still absolute and won't change.

But a better solution would be to use a formula which makes the use of conditional format not necessary:
=IF(AND(A4<>"",A5<>""),A5-A4+1,"")

Best regards,
Trowa