Forum Discussion
Solved
Conditional Formatting Based on Other Formulas
=AND(date-order_by<=0, current<0)
date: pulls the date above any selected cell
order_by: workday(today(), lead time)
current: the cell that it on
if the and statement is true then it fills red.
I'm trying to find a way to mark the first column if the given row has a red highlight.
Thanks. The definition of date_row should be
=Sheet1!$D$1:$AW$1
instead of
=Sheet1!$D$1:$AW$2
I have sent the file with this modification back by PM.
8 Replies
Try a rule with formula
=COUNTIFS(date_row,"<="&WORKDAY(TODAY(),lead time),data_row,"<0")
where date_row is the row with dates at the top (absolute reference), and data_row is the first row with numbers (relative reference).
I can get the formula to work if I use only one criteria and delete the other. When I put them together I get #VALUE. Thank you for the help.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
