Forum Discussion
Trying to use IFS formula to create a Data Validation field
Hello, I'm trying to use the IFS field to create a data validation entry where it picks the list for the 2nd column based on the selection in the first column. I was able to successfully do this for up to 6 entries but then it won't let me enter anything else. You can see this in the tab "End User Lists" B3. I did this successfully as well for up to 4 drop downs in tab "Named Lists" B18. I thought the IFS worked with up to 127. How do I work around this? What other options were there for what I'm trying to do. I'm trying to do this with the Curriculum/Course fields, Function/SubFunction fields where it picks the 2nd based on the entry of the first.
Please help. I'm not the most Excel savvy but I'm trying to learn and was so excited when I figured this out, only for it to not work past 6 options.
I'm using Office365 for Enterprise on a Windows 7 laptop.
Thank you in advance.
- 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.
8 Replies
- 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.
See the attached version. You'll have to complete column I on the Named List sheets.
- Thank you for your help!
