combining conditional formatting and functions
Hi all,
I want an empty given cell, say AA5, to become the color red when a range of 8 cells in the same row starting 13 columns to the left of AA5 (i.e. the range is N5:U5) is blank. (To explain, if a payment was not made over the 8 recent months, I want to get the automatic red "alarm".) This probably involves: - conditional formatting of AA5 (to get the red color) - the IF function (IF the range is blank) - the OFFSET function (OFFSET(AA5, 0,-13,1,8) Each month I insert a new column for the new month, making the given cell AA5 become AB5, but the range (8 cells in the same row starting 13 columns to the left) stays the same. That is why I think the OFFSET function should be used. I cannot manage to put it all together and make it work. Any help appreciated. Thanks. Zvi |
combining conditional formatting and functions
Use conditional formatting with that formula
=COUNTA(OFFSET($AA$5,0,-13,1,8))=0 When in C F, change Condition 1 to Formula Is -- HTH Bob Phillips (remove nothere from email address if mailing direct) "zvi" wrote in message oups.com... Hi all, I want an empty given cell, say AA5, to become the color red when a range of 8 cells in the same row starting 13 columns to the left of AA5 (i.e. the range is N5:U5) is blank. (To explain, if a payment was not made over the 8 recent months, I want to get the automatic red "alarm".) This probably involves: - conditional formatting of AA5 (to get the red color) - the IF function (IF the range is blank) - the OFFSET function (OFFSET(AA5, 0,-13,1,8) Each month I insert a new column for the new month, making the given cell AA5 become AB5, but the range (8 cells in the same row starting 13 columns to the left) stays the same. That is why I think the OFFSET function should be used. I cannot manage to put it all together and make it work. Any help appreciated. Thanks. Zvi |
combining conditional formatting and functions
Bob,
Your solution looks good. Thanks, Zvi |
All times are GMT +1. The time now is 02:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com