SOLVED

Drop Down List formula based on data in a different cell

Copper Contributor

I wrote a formula for a drop down list based on the data of another cell (see below). What am I missing?

 

The drop down list should be the percentages if the data in another cell is a specific result.

 

=IFS(P4=“Meets Expectations”,“2.75,3.00,3.25”,P4=“Above Expectations”,“3.50,3.75,4.00”,P4=“Exceptional”, “4.25,4.50,4.75”,P4=“Needs Improvement”,“1.00,1.50,2.00”,P4=“Below Expectations”,“0.00”,P4=“New Hire”,“0.00”)

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@BeverlyHV 

 

Attached is what I've called a cascading data validation (AKA drop down). See if you can follow the approach used there. It does depend on your having the most current version of Excel

@BeverlyHV 

 

As a follow up, I believe I have the formula correct, however the entire formula is showing in the drop down list. How do I fix the formula so only the actual list (percentages) show as options based on the rating in a different cell.

@BeverlyHV 

You should follow the example posted by Mathetes: using a range as source instead of a text string.

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@BeverlyHV 

 

Attached is what I've called a cascading data validation (AKA drop down). See if you can follow the approach used there. It does depend on your having the most current version of Excel

View solution in original post