balls. The 6 other subcategories/column names are:
- Financial Status
- Quality Status
- Staffing Status
- Delivery/Operations Status
- Customer Satisfaction Status
- Subcontractor Management Status
The Overall Status is green if all subcategories are green.
The Overall Status is yellow if 1-3 subcategories are yellow.
The Overall Status is red if 4+ subcategories are yellow or 1+ subcategories are red..
Here is the formula that I’ve completed that works correctly; however, when I try to add the next (IF(AND)) part, it stops as if I’ve hit a maximum number of characters. Is that the case? If so, is there a shortcut that I’m not finding to make this more manageable?
=IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Green", IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Yellow", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Yellow", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Yellow", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Green", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Yellow"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Yellow", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Yellow", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Yellow", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Green", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Yellow"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Yellow", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Yellow", [Customer Satisfaction Status]@row = "Green", [Subcontractor Management Status]@row = "Green"), "Yellow", (IF(AND([Financial Status]@row = "Yellow", [Quality Status]@row = "Yellow", [Staffing Status]@row = "Green", [Delivery/Operations Status]@row = "Green", [Customer Satisfaction Status]@row = "Yellow", [Subcontractor Management Status]@row = "Green"), "Yellow")))))))))))))))))))))))))))
Thanks,
Lori