Forum Discussion
Trying to use IFS formula to create a Data Validation field
- So I discovered something that I'm leaving here in case anyone else has the same problem.
The issue wasn't the IFS statement but that I was trying to use it in the Source field in Data Validation. Apparently that field can only hold 256 characters. I tried to short the names of the tab and the lists but it wasn't enough. I played around with it all a bit more and realized that I can name Formulas. So, I created my entire IFS statement as a FORMULA, named that, and then in the data validation, just called to the formula. It works, I was able to use full names and didn't have to shorten anything.
If you have the newest version of Excel, with the ability to do Dynamic Array functions, here's another approach to having a secondary drop down list, with members dependent on the first. It's a sample I created as a demonstration....pick a first name, and you'll see that the list of potential last names changes. You can extend the table in columns A and B to see how the lists can grow indefinitely.
'
Thanks. This does work, however what about if you have a table and not just a first name field and a last name field.
For example, I have a table that each line is different and the second list should be dependent on the first list selection, for each line. Each line may have a different choice for the first list selection. Basically, I have a category list as list 1 and a sub category list as list 2 that is dependent on list 1 selection.
Hope this makes sense.
- My example, of course, was just that: an example of a concept, an example of what could be done. From what you've written I see no conceptual difference between Last and First Names and Category and Sub-Category. Perhaps you could provide a more complete example, or an image to illustrate what that table looks like.
Can you please help me with this? These, (Acquiring, Applying, Leveraging, and Influencing) are used in a validation list and if one of these are selected, I want it to display the percentage in the cell next to it
=IF(D5=Acquiring, "25%",D5="Applying", "50%",D5=Leveraging, "75%",D5="Influencing", "100", "")Your formula could be
=IF(D5=Acquiring, 25%, IF(D5="Applying", 50%, IF(D5="Leveraging", 75%, IF(D5="Influencing", 100%, ""))))
or
=IFS(D5=Acquiring, 25%, D5="Applying", 50%, D5="Leveraging", 75%, D5="Influencing", 100%, TRUE, "")
Alternatively, create a small lookup table:
In this screenshot, the lookup range is J2:K5. You can then use
=XLOOKUP(D2, $J$2:$J$5, $K$2:$K$5, "")
or
=IFERROR(VLOOKUP(D2, $J$2:$K$5, 2, FALSE), "")
