combining conditional formatting and functions

[zvi]

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

 

[Bob Phillips]

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]

Bob,

Your solution looks good.

Thanks,

Zvi