Create dependent drop down lists containing unique distinct values in multiple rows
How can i use these list for multiple rows?
I would like to use these lists for multiple rows and let people enter detailed records.
Answer:
In my example workbook, I have created three sheets:
- Multiple rows
- Data
- Calculation
The selected drop-down list gets all values from a single column on sheet "Calculation".
The array formula on sheet "Calculation" has to "know" which cell you have selected and the adjacent cell value on sheet "Multiple rows".
Setup an automatic event on sheet "Multiple rows" (VBA)
- Press Alt-F11 to open VB editor
- Double press with left mouse button on Sheet1 "Multiple rows" in project window.
- Copy and paste vba code below into code window.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Calculation").Range("D1") = Sheets("Multiple rows").Range("A" & ActiveCell.Row)
End Sub
Cell D1 on sheet "Calculation" is updated instantly whenever you select a cell on sheet "Multiple rows"
Setting up the "Calculation" sheet
Array formula in cell B2:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell B2 and paste it down as far as needed. You can read how this formula works here: 5 easy ways to extract unique distinct values
Dependent drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on […]
Excel categories
15 Responses to “Create dependent drop down lists containing unique distinct values in multiple rows”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I have no words to thank you
Can I add 3rd, fourth and fifth column of criteria such as size and color?
Very clear and helpful. I echo Sharmila's post. Can't thank enough.
Sandi B,
I am happy you like it! There is an alternative version with no vba:
Dependent data validation lists in multiple rows
Thank you Oscar!
exist too solution for google spreadsheet? everything works, but I have problem only with the application to next rows.
Sievert,
I have problem only with the application to next rows.
Can you explain in greater detail?
Please Oscar, look at the link - https://www.mrexcel.com/forum/excel-questions/669408-dependent-drop-down-lists-google-spreedsheet-values-multiple-rows.html .
I tried to more explain.. Thank you
Sievert,
Only one thing doesn't work Application function to the next rows.. I tried use timestamp, but without successful, maybe I used wrong it.
I still don´t understand and I don´t know much about google spreadsheets.
Sorry, my mistake..
When you open spreedsheet :
https://docs.google.com/a/linksoft.cz/spreadsheet/ccc?key=0Asiaoa2t4PpGdGJDWk5wSE43LVdyTXJHU3R0SHdkY0E#gid=1
look at the row 2, dependent list works correctly, when you look at the row 3, dependent list doesn´t work.. because now you can see values B1 and B2... but still you can see values from the previous row 2... so function works only for second row and the multiple rows doesn´t work :(
Thank you very much. So clear and very well described. Hours saved.Great help.
Hi Oscar,
I utilized your sample file because I wanted to add a 3rd and 4th column. However, the dependent drop-downs that I created (using the same array formulae and named ranges as the 1st and 2nd columns) do not work. The new columns just do not show any value.
Is this due to the VB only firing once for the value in the first column?
Happy to upload my sheet to explain further if needed.
Many thanks!
hi ,i am creating a dependent drop down list with values in it , i found different links for the same but they are not so sufficient. now in my drop down list the first list is in column B having values which are in percentage link 100%,90%,88%,75% and in column E their are another list having error code like 2A,2B,2C,2D ,and for the column g their are error code description like options type incorrect,incorrect builder's (i can use vlookup here to found out what description stand against the error code like 2A=options type incorrect,i think i can do this part) now here the problem starts first i serached on google and found out this link https://www.contextures.com/xlDataVal02.html ,in this link their is a process to name the columns in the name box as they are my value based so iam unable to name them ,(we can just write alphabets only in the name box ,then i googled again started forum at for https://www.mrexcel.com/forum/excel-q...ml#post4153088 ,nice guy told me to put "A" in front of your cell but this is not the way i want if i put the alphabet then after giving them scoring i again need to replace it. i again googled and found this link https://www.get-digital-help.com/2010...lues-in-excel/, this was really was of my work i am unable to understand it as it is so advanced for me right now. i like the way where you just name them and get started but you know it won;t accept numbers and i want to number them only. link at get digital is exactly as i need but so difficult ,is their any other way to do with ease ,if not please explain me the get help forum and help me to create this sheet . i am unable to attach right now my sheet.
Hello Oscar,
I'm not sure if you're still monitoring these older posts, but I figure I would send along a thank you for your wonderfully clear tutorials.
I'm working through adapting this for a third column. I am able to successfully work with a third column in the 'Multiple rows' sheet where column B only has one category in the dropdown. For instances where there are multiple in B, they cease to function. I'm wondering if it is something as simple as refining the VBA code to account for the added range (no longer "A", but "A:B")?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Sheets("Calculation").Range("E1") = Sheets("Multiple rows").Range("A" & ActiveCell.Row) End Sub 'something like this instead: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Sheets("Calculation").Range("E1") = Sheets("Multiple rows").Range("A:B" & ActiveCell.Row) End Sub 'though this throws back and errorI hope this message finds you well. Any help would be much appreciated. I'll continue to dig and will be sure to upload the solution if I figure it out.
Thanks again for all your posts. They are great.
Your efforts, continuing along from the first posts on this topic, have helped me very much and saved a ton of time. Thank you for making all of your work available!
Michael Norelli,
thank you!