Highlighting a blank cell

[Peta Durose]

I am really hoping someone can help, I feel like this is an easy one, but I just can't get it.

I have a workbook to manage my work load.
Sheet one is a summary of all my projects
Sheet two and beyond is separated by project and the work required with due dates.

In sheet 2, it has a list of tasks and dates they are due (F16:F22) - I have conditional formatting on these to highlight anything that is within the next 7 days.

In sheet 1, I want to have a reminder column - so if ANY column is highlighted in Sheet 2 F16:F22 it will highlight in one cell (I2).

Screenshots attached.

Thank you in advance!!

 

[Asbestos_Jen]

You can put a formula in your reminder cell and set the conditional formatting based on it like this:

Book1
	A	B	C	D	E	F	G	H
1	Project	Impact	Go Live	Status	Team	Lead	Notes	Reminders
2	Project 1							DUE SOON
3	Project 2
4	Project 3
Sheet1

Cell Formulas
Range	Formula
H2	H2	=IF(Sheet2!F16:F20<=TODAY()+7,"DUE SOON","")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peta Durose]

You are amazing! Thank you!!

 

[Asbestos_Jen]

Glad I could help. Thanks for the feedback!

 

[Peta Durose]

Hey again, I thought it worked but played around a little.
It is showing the value "DUE SOON" even if there is no value in the due date column.
Is it just the way I am doing it?

 

[Asbestos_Jen]

The formula is treating blank cells as 0, which is less than today()+7. You will need to use a lower boundary in addition to the upper boundary if sometimes cells may be blank. What is the formula you are using to colour the cells in sheet2?

 

[Peta Durose]

in sheet 2, I was just using conditional formatting.
I am not worried so much if it is not highlighting in sheet 2 - as long as it is showing in sheet one if something is due within 7 days.

 

[Asbestos_Jen]

Right, but what is the formula in your conditional formatting? We can adapt that formula to your sheet1 reminders.

 

[Asbestos_Jen]

Something like this should work:

=IF(COUNTIFS(Sheet2!$F$16:$F$20,">="&TODAY(),Sheet2!$F$16:$F$20,"<="&TODAY()+7)>=1,"DUE SOON","")