Forum Discussion

robustexcel's avatar
robustexcel
Copper Contributor
Jun 11, 2020
Solved

Data Validation cascading

I have a form to fill in excel with two columns First Name and Last Name. Both are validation lists and the last name should be dependent on the value of the first name. I have a total of 400 names. ...
excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Jun 12, 2020

    robustexcel 

    The attached might work in Excel 2016, but I have no way of finding out.  Working without FILTER and UNIQUE is somewhat like trying to use a lawnmower to trim a hedge; simply the wrong tool for the job!

     

    p.s. I have only just looked at the file you attached showing your Excel version.  If it is Office 365 monthly then it is very odd that the original files do not work for you.

     

mathetes's avatar
mathetes
Silver Contributor
Jun 11, 2020

robustexcel 

 

Here you go. I didn't create the form, but assume you can do that. This is an illustration of what the new Dynamic Array functions can perform. I used FILTER, UNIQUE, and SORT in this. You can add names to your heart's content in the table in the top left corner of the sheet. The drop-downs will automatically include the new ones, in alphabetical order.

 

Enjoy!

robustexcel's avatar
robustexcel
Copper Contributor
Jun 11, 2020

Hi mathetes,

 

Thank you for helping out.  I am getting this in all the cells that you have a formula in. 😞

#NAME?

 

I have excel 2016

Resources