Drop Down List formula based on data in a different cell

New 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)



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



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.


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