Conditional Formatting

[EDSTAFF]

I have a multi-sheet file. Sheet 1 is Roster of employees. Sheet 2 is Master Sheet of all staff certifications. Sheets 3-10 is individual certifications.
Sheet 1, D13:D52 is list of employees using direct input. Sheets 2-10 Column D is linked to Roster sheet via formula "="ROSTER!D13", etc.
Column F on sheets 2-10 is Renewal Dates of certifications.
Column G (hidden) is simple forumua "=TODAY()
Column H, 13-52 has formula "=IF(-(G9-F9)=0,"",-(G9-F9))"
Conditional Formatting on Column H, 13-52
If <0, Red (Certification Outdated)
If 1-180, Yellow
If >180 Green

All formulas work ok UNLESS I remove a roster name from Sheet 1. If an entry is removed from Sheet 1, D13:D52, Sheets 2-10 displays a "O" in columns D, E and calculations are still performed in Column H, including Conditional Formatting.

How can I link Column D on sheets 2-10 to make it skips blank entries on Sheet 1 "Roster" to avoid the messiness of colored cells where no entry exists. Is there an IGNORE/BLANK function that will accomplish this?

TIA

 

[RAM]

Hi and welcome to the board!

Wouldn't it work if you linked your roster list from Sheet1 to Sheet2, Column D, with the same IF-formula you're already using, like:

=IF(Roster!D13="","",Roster!D13)

On my sheet it leaves the linked cell in column D blank instead of displaying 0.

HTH