Cell Referencing problem when copying conditional formatting
(Excel 2000)
I have a row of cells (K6:AH6) which have some conditional formatting,
depending whether or not the date (month and year) shown in the corresponding
headers in K5:AH5 are less than or greater than (up to 12 months) the date
given in cell D6.
I have managed to get this working for one row, using the following formula:
For the months that are less than the given date in D6 (up to 12 months
previous):
=AND(DATE(YEAR(K5),MONTH(K5),28)<DATE(YEAR($D$6),M ONTH($D$6),28),DATE(YEAR(K5),MONTH(K5),28)DATE(YE AR($D$6)-1,MONTH($D$6)-1,28))
For the months that are greater than the given date in D6 (up to 12 months):
=AND(DATE(YEAR(K5),MONTH(K5),28)DATE(YEAR($D$6),M ONTH($D$6),28),DATE(YEAR(K5),MONTH(K5),28)<DATE(YE AR($D$6)+1,MONTH($D$6)+1,28))
When I copy this down to the other rows, the cell reference changes where a
'$' has not been entered in the formula - so the references for the headers
then changes to subsequent rows. However, I change the formula to how I
think it should be represented, the conditional formatting doesn't work.
|