Forum Discussion

Jgolden1980

Copper Contributor

Sep 15, 2022

Solved

IF(OR Statements from multiple sheets/tables

Hello. I'm trying to write a formula based on dates in tables on different sheets. What i want is a cell on the first sheet to fill red and show "Passed due" if any of sheet2 E4:E8 = (Today() - E$)...

Formulas and Functions

HansVogelaar
Sep 15, 2022
Jgolden1980

In B7:

=IF(COUNTIF(INDIRECT(SUBSTITUTE(B6,"-","")&"[Date Complete]"),"<"&TODAY()-7),"Past Due","")

Fill to the right to G7.

Select B7:G7.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format on;y cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'equal to' from the second drop down.

Enter Past Due in the box next to it.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

See the attached version.
PM Tracker.xlsx42 KB

Harun24HR

Bronze Contributor

Sep 15, 2022

Attach a sample worksheet.

Jgolden1980

Copper Contributor

Sep 15, 2022

https://docs.google.com/spreadsheets/d/13DztM9R_GoYqX8oZOLAnvjIjMTUGMaQq/edit?usp=drivesdk&ouid=115482733269670306841&rtpof=true&sd=true

Not sure if this would work

HansVogelaar
MVP
Sep 15, 2022
Jgolden1980

In B7:

=IF(COUNTIF(INDIRECT(SUBSTITUTE(B6,"-","")&"[Date Complete]"),"<"&TODAY()-7),"Past Due","")

Fill to the right to G7.

Select B7:G7.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Format on;y cells that contain'.

Leave the first drop down set to 'Cell Value'.

Select 'equal to' from the second drop down.

Enter Past Due in the box next to it.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

See the attached version.
PM Tracker.xlsx42 KB
- Jgolden1980
  Copper Contributor
  Sep 15, 2022
  Perfect! Thank you so much for the help