Using cell A1 to reflect status of A2-A15

Hello,

I have conditionally formatted A2-A15 to visually represent a stoplight for a series of equipment dependencies (RED, YELLOW, GREEN).

I have used r=RED... y=YELLOW and g=GREEN for the conditional formatting factors.

What I am trying to do now is have cell A1 turn RED if any cell in A2-A15 is RED... YELLOW if cells A2-A15 are not RED but at least one is YELLOW... and GREEN if none of cells A2-A15 are RED or YELLOW

Basically, I am trying to get cell A1 to represent the top level equipment status for leadership and we can then drill down as required.


Thank you for your time and assistance.