Dependent dropdown list

%3CLINGO-SUB%20id%3D%22lingo-sub-1764840%22%20slang%3D%22en-US%22%3EDependent%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764840%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20lot%20of%20pump%20data%20on%20sheet%202%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20create%20a%20drop%20down%20list%20on%20sheet%201%20beside%20%22Cornell%20Model%22%20that%20would%20have%20a%20list%20of%20all%20unique%20pump%20models%20(Sheet%202%2C%20Column%20A)%20(remove%20duplicates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20create%20a%20dropdown%20on%20sheet%201%20beside%20%22RPM%22%20that%20would%20only%20show%20the%20unique%20RPMs%20available%20for%20that%20pump%20(sheet%202%20Column%20C)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20create%20a%20dropdown%20son%20sheet%201%20beside%20Hp%20that%20would%20only%20show%20the%20unique%20Hp%20motors%20for%20that%20pump%20model%20and%20the%20RPMs%20selected%20(sheet%202%20Column%20B)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20appreciate%20any%20help%20and%20advice%20on%20how%20to%20achieve%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1764840%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1765388%22%20slang%3D%22de-DE%22%3ESubject%3A%20Dependent%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765388%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826591%22%20target%3D%22_blank%22%3E%40hooligan68%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThe%20solution%20can%20also%20be%20via%20a%20......%20pivot%20table.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3EHere%20is%20a%20small%20example%20of%20what%20I%20found%20on%20the%20Internet%2C%20you%20just%20have%20to%20adapt%20it%20to%20your%20workbook.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20was%20able%20to%20help%20you%20and%20would%20be%20happy%20to%20know%20if%20I%20could%20help%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1765807%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20dropdown%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765807%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826591%22%20target%3D%22_blank%22%3E%40hooligan68%3C%2FA%3E%26nbsp%3BGoogle%20for%20%22dependant%20dropdown%20Excel2%20and%20you'll%20find%20many%20tutorials%20on%20how%20to%20achieve%20this.%20In%20case%20you%20are%20on%20a%20recent%20Excel%20version%20that%20supports%20Dynamic%20Array%20functions%20(Like%20UNIQUE)%2C%20have%20%26nbsp%3Ba%20look%20at%20the%20attached%20file%20an%20see%20if%20it%20works%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have a lot of pump data on sheet 2

 

I would like to create a drop down list on sheet 1 beside "Cornell Model" that would have a list of all unique pump models (Sheet 2, Column A) (remove duplicates)

 

Then create a dropdown on sheet 1 beside "RPM" that would only show the unique RPMs available for that pump (sheet 2 Column C)

 

Then create a dropdown son sheet 1 beside Hp that would only show the unique Hp motors for that pump model and the RPMs selected (sheet 2 Column B)

 

I would appreciate any help and advice on how to achieve this.

4 Replies
Highlighted

@hooligan68 

 

The solution can also be via a …… pivot table.

Here is a small example of what I found on the Internet, you just have to adapt it to your workbook.

 

Hope I was able to help you and would be happy to know if I could help you.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@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.

Highlighted

@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

Highlighted

@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.