Hello! I'm struggling to complete this formula without receiving errors. I am currently attempting to create formula in a project tracking sheet to return colored symbols (R,G,Y,G) into a "Project Health" Column. Below is the formula that I have so far. It is working up to this point, but when I try to add on IF statements (I've been attempting to utilize the best practice of building logical statements, one by one) to return the "red" dot, I receive error messages.
=IF(Status@row = "Complete", "Green", IF(Status@row = "Holding", "Gray", IF(Status@row = "Not Needed", "Gray", IF([Approval Status]@row = "Declined", "Yellow", IF(Deadline@row = TODAY(-7), Status@row <> "Complete", "Yellow")))))
The following is a set of all criteria I'm hoping to meet:
- "Project Health" turns "Green" for any given row that has a status of "Complete" or if the Deadline is "3 weeks out or further"
- "Project Health" turns "Yellow" for any given row that does not have a status of "Complete", "Holding", or "Not Needed", and the deadline is within the next 7 Days
- "Project Health" turns "Gray" for any given row that has a "status" of "Holding" or "Not Needed"
- "Project Health" turns "Red" for any given row that does not have a "Status" of "Complete", "Holding", or "Not Needed", and the deadline is within the next 3 days or is in the past.
Thanks in advance for the help!