Forum Discussion
Hello
How can I get four different cells with drop down functions with each cell (eg: cells: A23 with drop down selections of red, orange and green, Cell A25 with drop down selection red , orange and green, etc)
To provide a recommendation at the bottom of the spreadsheet ie: if all cells selected were green the outcome is A, if more than 2 cells were orange the outcome is B, if one cell is red the outcome is C
If 4 different cells with 3 options (all cells have a value), there will be 15 outcome.
Green Orange Red 4 0 0 3 1 0 3 0 1 2 2 0 2 1 1 2 0 2 1 3 0 1 2 1 1 1 2 1 0 3 0 4 0 0 3 1 0 2 2 0 1 3 0 0 4 If the number of green * 1 + the number of orange * 10 + the number of red * 100, you will have a sorted value list {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}.
Assume the 4 different cells are A23:A26 and the result in A27.
The formula in A27 is
=INDEX( {"A","A-","A-","B","B","C","C","C","C","C","C","C","C","C","C"}, 1, MATCH( COUNTIF(A23:A26,"Green")+ COUNTIF(A23:A26,"Orange")*10+ COUNTIF(A23:A26,"Red")*100, {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}, 0 ))This approach is not good. I think there should be a better approach from other helpers.
14 Replies
= IFS( COUNTIFS(Table1[@], 4) > 0, "Refer to Manager", COUNTIFS(Table1[@], 2) > 1, "Investigate", COUNTIFS(Table1[@], ">0") = 3, "Affirmed", 1, "")- Thank you
If 4 different cells with 3 options (all cells have a value), there will be 15 outcome.
Green Orange Red 4 0 0 3 1 0 3 0 1 2 2 0 2 1 1 2 0 2 1 3 0 1 2 1 1 1 2 1 0 3 0 4 0 0 3 1 0 2 2 0 1 3 0 0 4 If the number of green * 1 + the number of orange * 10 + the number of red * 100, you will have a sorted value list {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}.
Assume the 4 different cells are A23:A26 and the result in A27.
The formula in A27 is
=INDEX( {"A","A-","A-","B","B","C","C","C","C","C","C","C","C","C","C"}, 1, MATCH( COUNTIF(A23:A26,"Green")+ COUNTIF(A23:A26,"Orange")*10+ COUNTIF(A23:A26,"Red")*100, {4,13,22,31,40,103,112,121,130,202,211,220,301,310,400}, 0 ))This approach is not good. I think there should be a better approach from other helpers.
- No only 1 of 3 outcomes and all based on the colour selection for the drop down tabs in each cell.
Option 1 - recommendation affirmed (if all green)
Option 2 - investigate further (if 2 or more orange was selected regardless of green colour)
Option 3 if one or more red is selected regardless of orange then escalate it to manager - Thanks for the reply Willy 🙂
Ok I’ll try an make it a bit clearer so I have the following cells with drop down tabs
A23 (with drop down tabs - Green, orange,red, pending)
A25 - Green, orange red pending
A27 - green orange red pending
A29 - green orange red pending
A31 - green orange red pending
Next under these cells in one cellI want it to spit out a recommendation according to which or how many colour coded tabs were selected such as -
All green for the above cells. - recommendation affirmed
Or
2 orange cells investigate further
Or
1 red cell - refer to manager
Does that make better sense of what I’m trying to achieve?
Thanks for your help
- That’s ok because all green is affirmed
And 2 or more orange regardless of green is to investigate further
And one or more red regardless of other colours is to escalate it - Ok I see what you mean so if they’re all green that’s recommendation affirmed
If there is 2 or more orange then the recommendation is to investigate further
If there is 1 red or more then the recommendation is to escalate it to the manager.
Does that make the formula a little less complicated?May I know which excel version that you are using in your working place? some functions do not exist in old version.
