Here is a scenerio of the data being used
Cell B3 has today's date.
A5-Tag# B5-Descript C5-LastDate D5-DueDate E5-Status
============================================================
12345 Widget #1 4/24/2007 4/24/2008 OK
23456 Widget #2 1/3/2007 1/3/2008 DUE
34567 Widget #4 10/24/2006 10/24/2007 OVERDUE
Blank Blank Blank Blank OVERDUE
The above is conditionally formatted so if OK, the cell is green, if Due its yellow and if overdue its red.
The formula used is as follows:
=IF(2*(D6<$B$3)+1*(D6-$E$3<=$B$3)*(D6>=$B$3)=0,"OK",IF(2*(D6<$B$3)+1*(D6-$E$3<=$B$3)*(D6>=$B$3)=1,"DUE","OVER DUE"))
This works great until you copy the formula down past the last record so the column is set and ready to go for the next entry.
PROBLEM... in all lines with no data, Column # shows RED Cells saying OVERDUE. I've tried to add another IF statement to this formula and I continually get "You've entered too many arguments".
I wanted to add an if, that if D is "" (empty) that the cell would remain "" empty or show no color and no status such as OK, DUE or OVERDUE.
How can I add another IF to this so that will happen? For reasons I cannot get into, the response for this, I need tweaking of the forumula. It may be real easy using code but it HAS to be this formula. If not, then just respond to let me know that I'm truly out of luck here.
I appreciate your time and attention.
LadyCK3
aka: Laurie
Cell B3 has today's date.
A5-Tag# B5-Descript C5-LastDate D5-DueDate E5-Status
============================================================
12345 Widget #1 4/24/2007 4/24/2008 OK
23456 Widget #2 1/3/2007 1/3/2008 DUE
34567 Widget #4 10/24/2006 10/24/2007 OVERDUE
Blank Blank Blank Blank OVERDUE
The above is conditionally formatted so if OK, the cell is green, if Due its yellow and if overdue its red.
The formula used is as follows:
=IF(2*(D6<$B$3)+1*(D6-$E$3<=$B$3)*(D6>=$B$3)=0,"OK",IF(2*(D6<$B$3)+1*(D6-$E$3<=$B$3)*(D6>=$B$3)=1,"DUE","OVER DUE"))
This works great until you copy the formula down past the last record so the column is set and ready to go for the next entry.
PROBLEM... in all lines with no data, Column # shows RED Cells saying OVERDUE. I've tried to add another IF statement to this formula and I continually get "You've entered too many arguments".
I wanted to add an if, that if D is "" (empty) that the cell would remain "" empty or show no color and no status such as OK, DUE or OVERDUE.
How can I add another IF to this so that will happen? For reasons I cannot get into, the response for this, I need tweaking of the forumula. It may be real easy using code but it HAS to be this formula. If not, then just respond to let me know that I'm truly out of luck here.
I appreciate your time and attention.
LadyCK3
aka: Laurie