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. |
All times are GMT +1. The time now is 10:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com