Forum Discussion
Dependent dropdown list
hooligan68 Google for "dependant dropdown Excel2 and you'll find many tutorials on how to achieve this. In case you are on a recent Excel version that supports Dynamic Array functions (Like UNIQUE), have a look at the attached file an see if it works for you.
Riny_van_Eekelen Thank you for the advise. I have looked up the tutorials for dependent drop down lists but most of them are too simple for what I am trying to do.
Unfortunately, my Excel does not support the Dynamic Array functions, even though I have office 2019. Also, the spreadsheet will be used by sales to help price one of the items that we sell quicker. Many of them use older versions of Excel.
I have been trying to use pivot tables to sort the data and I think I was successful on sheet 4. I still do not know how to make the drop downs on sheet 1 reflect the data on the pivot table. What I would to be able to do is to select the Model, then have the drop down for RPM display only the RPM associated with that model. Once the RMP has been selected, have the drop down for HP show only the HPs associated with that Model and RPM.
Can you assist with a method to do that without using Dynamic Array functions? I greatly appreciate it.
Paul
hooligan68 Okay! Understood. Then the easiest way to set it up and maintain it for the future would be (in my opinion) to use named ranges and have the data validation lists point to these named ranges using the INDIRECT function. I have done that for two products as an example (Sheet 3), with data validation in Sheet1 for Model, RPM and hp. It's a bit of work to set it up, but once done it will be fairly easy to maintain without the need for long and complicated formulae. I'm not a fan of such formulae.
