## Create dependent drop down lists containing unique distinct values in multiple rows

*Article last updated on February 25, 2018*

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 click 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"

### Setup "Calculation" sheet

Array formula in cell B2:

Copy cell B2 and paste it down as far as needed.

**Download example workbook**

Download excel sample file for this tutorial.

unique-distinct-dependent-lists_multiple_rows.xls

(Excel 97-2003 Workbook *.xls)

**Recommended posts:**

- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Create dependent drop down lists containing unique distinct values in excel
- Apply dependent combo box selections to a filter in excel 2007

Functions in this article:

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array, row_num, [column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**COUNTIF(**range, criteria**)**

Counts the number of cells within a range that meet the given condition

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Apply dependent combo box selections to a filter

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

Dependent data validation lists in multiple rows

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent […]

Invoice template with dependent drop down lists

Overview This post descibes how to use a basic invoice template I created. The invoice template let´s you use dropdown […]

Dependent drop down lists – Enable/Disable selection filter

Josh asks: I have this working right now with 6 drop downs/lists. I wanted to see if you possibly know […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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")?

I 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!