## Dependent data validation lists in multiple rows

*Article updated on August 29, 2017*

This post describes how to setup data validation lists in a column and dependent data validation lists in an adjacent column. There is no vba code in the example demonstrated here.

### Named ranges

The following named ranges expands automatically when new text values are added.

Name: Product

Formula:

Learn more about the OFFSET and MATCH function:

How to use the OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]

Identify the position of a value in an array.

Name: Region

Formula:

**Array fomulas**

The first array formula returns unique distinct values in cell G1:L1 from named range *Region. *The second array formula in cell range G2:L7 returns coherent values from each region. All of these calculations can be hidden or placed on a calculation sheet.

Array formula in cell G1:

Learn more about INDEX function:

Gets a value in a specific cell range based on a row and column number.

Array formula in cell G2:

Learn more about SMALL function:

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

**How to create an array formula**

- Select a cell
- Type array formula
- Press and hold Ctrl + Shift
- Press Enter

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Copy cell G1 and paste to cell range H1:L1. Copy cell G2 and paste to cell range G2:L7.

**Named ranges**

Now it is time to create the two last named ranges that will be used in data validation lists.

Name: UniqueRegion

Formula:

Name: UniqueProduct

Formula:

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

**Data validation lists**

- Select cell range D2:D9
- Go to tab "Data"
- Click "Data validation" button
- Select "List"
- Type =UniqueRegion
- Click OK

Repeat above steps with cell range E2:E9 and Source: =UniqueProduct.

This article shows you how to search using a drop down list:

Use a drop down list to search and return multiple values

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]

**Download excel *.xlsx file**

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 […]Create dependent drop down lists containing unique distinct values in multiple rows

Sharmila asks: How can i use these list for multiple rows? I would like to use these lists for multiple […]### 29 Responses to “Dependent data validation lists in multiple rows”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar instead of using "ZZZZZ", use a "Ω" to the same effect. Applicable only for finding the last Text.

chrisham,

thanks!

Why did you choose 'Dependent values'!$D13 for the UniqueProduct formula?

Paulo,

You found an error, I have uploaded a new file and made some corrections to this post.

Thanks for bringing this to my attention!

Hi Oscar,

What about my data list of the Product have duplicated value and I only want to show unique value?

=INDEX(Product, SMALL(IF(G$1=Region, MATCH(ROW(Region), ROW(Region))), ROW(A1)))

Also, how can I make a list to match 2 value?

Here is my data sample ( My lst is huge!)

Division Dept Line Subline

Women D121 Tops T-shirts

Women D121 Bottom Plants

Women D123 Tops Jacket

Women D121 Tops Jacket

Mens D124 Bottom Plants

Mens D124 Bottom Underwear

Mens D125 Bottom T-shirts

..............................

Before, I use vba to write the data validation. However as the list is too large. The excel sometimes become no responding and take times to write the unique list.

Thank you!!!!!

Penny,

Before, I use vba to write the data validation. However as the list is too large. The excel sometimes become no responding and take times to write the unique list.I don´t think a formula is faster for filtering unique distinct values. I would suggest using vba and pivot table:

Excel vba: Populate a combobox with values from a pivot table

Change pivot table data source using a drop down list

Dependent Drop Down Lists AddIn

Why you used "zzzzzz" what it will do. can we add "sukhdev" in place of "zzzzzz". will it work or not??? kindly let me know.

Sukhdev,

MATCH("ZZZZZZZZ", 'Dependent values'!$A:$A)-1 returns the last cell row containing a text string. You could use this value: ÿ instead to make sure all text strings are matched.

What should we use for Numbers (code) and text both. Please help me.

Sukhdev,

Try this:

=MAX(MATCH("ÿ",'Dependent values'!$A:$A),MATCH(9E+307,'Dependent values'!$A:$A))-1

Hi Oscar Thanks for reply

I tried the same in Name manager but did get success. I Used below formula in Name Manager;

=MAX(MATCH("y",Sheet1!$A:$A),MATCH(9E+307,Sheet1!$A:$A))-1 (for Column A)

=MAX(MATCH("y",Sheet1!$B:$B),MATCH(9E+307,Sheet1!$B:$B))-1 (for Column B)

and My data is below.

Material Group Material Name

1156 Mouse Optical Neo Usb

1187 UPS Black Armour 725

1187 UPS Black Armour 725

1112 COMPUTER M/M SPEAKER BEATS IT-1875 SUF R

1111 Computer Cabinet P4 Entizer W Smps & Usb

1111 Computer Cabinet P4 Supernova RB w smps

1111 Computer Cabinet P4 Orion RB w smps & us

1111 Computer Cabinet P4 Empower- R w smps &

1196 Data Card Speed-3Gv 7.2”(Modem)

1112 Computer Multimedia Speaker IT 2000 Sb J

Sukhdev,

No, I just pointed out the part of the formula that returned the error. Here is the whole formula:

=OFFSET(Sheet1!$A$2, 0, 0, MAX(MATCH("ÿ",Sheet1!$A:$A),MATCH(9E+307,Sheet1!$A:$A))-1)

See this file:

Sukhdev.xlsx

Hi Oscar,

Above mentioned Formula is working perfectly. Thanks a lot for helping me.

Thanks,

Best Regards,

Sukhdev

Sukhdev,

I am happy you got it working!

Hi Oscar

I have two columns Mcode and Mname and I wish to make dynamic drop down of unique Mcode. If I select One M code, all the Mname including duplicate under that mcode should reflect. My data is very huge so I want to create it dynamically. Please help me.

Thanks,

Regards

Sukhdev

Hi Oscar

Do you have a tutorial or example to do this exact same thing but using VBA?

Mike

Mike,

Sorry no.

VBA Example ?

Why you used 'Dependent values' what it will do. can we add delete this, will it work or not??? kindly let me know.

I don't want to use 'Dependent values'! Words in formula. But I can't. We don't use the another sheet, so why we use to urgency "!" sembol with sheet name?

I can't understand why we are matching with zzzzzzzz. I don't want to use match. Could you do this example with vlookup please. Thank you.

Need help with data similar to file Sukhdev.xlsx. Three dependent columns; first to have equipment type, second to have equipment brand and third to have the equipment model. I'm thinking I should have 2 sets of caluclation sheets similar to the one in tutorial. Is there an easier way?

Liju A,

I am not sure how, if you want to use array formulas.

My Dependent drop down list Add-In handles more than 2 columns easily.

or check out: https://www.contextures.com/xlDataVal13.html

Normally I don't learn article on blogs, but I would like to say that this write-up very compelled

me to check out and do it! Your writing taste has been surprised me.

Thanks, very great article.

hello everyone!

i'm trying to use a combo box filtered results based on a matching criteria between two sheets. please see example below.

Sheet1

A2(1/1)

A3(1/2

a4(1/3)

a5(1/4)

a6(1/5)

all the way to a99 with value (1/98) without repeating values on column A, unique values on sheet1!a:a

Sheet2

a2(1/1

a3(1/1)

a4(1/2)

a5(1/3)

a6(1/4)

a7(1/4)

a8(1/4)

and as you can see column a:a may have multiple concurrent rows with same value. repeating values on sheet2!a:a, these match sheet1!a:a

row number may go up as high as needed depending on what is needed to list from 1/1 to 1/98.

sheet2

b2(300)

b3(210)

b4(210)

b5(215)

b6(230)

b7(215)

b8(300)

repleating value on sheet2!b:b

so what i want is on sheet1 column B with combo box that will give me the to choose from list of values as example below

b2 combo box value options of 300 or 210

b3 combo box value option of 210

b4 combo box value option of 210

b5 combo box value options of 215

b6 combo box value options of 230, 215 and 300

etc all the way down to b99 matching rowcount with column a

sheet1!a:a is already filled with unique non repeating (1/x) values. these are the values tha repeat on sheet2!a:a

sheet3!a:a is already filled with unique no repeating (###) values. these are the values that repeat on sheet2!b:b

thank you in advance for your assistance and suggestions. i welcome your questions.

regards,

komdoree

Uniqueproduct isnt working.

Isn't there any simpler way of doing this?

Hi, I'm liking your tutorials, thanks for the work!

That said, it seems a little silly to create a dynamic range (as in one of your other examples) or have an automatically expanding range when you have to manually copy formula cells into a table. Is there any way to have that table automatically expand or to do this without having a reference table?

Thanks,

Hi Oscar,

Thanks for the posts.

My product list is not duplicate. Also When I am trying to create unique product in another sheet I am getting error.

Regards,

Naveen N

This is an interesting.

I don't like unique data is sorted by each Column.

I tried resorting it as By Row and have it still working but I keep failing. Where am I going wrong?

I changed all Row formula to Column and Row to Column to accommodate this changed.

For example

a - =INDEX(Region,MATCH(0,COUNTIF($F$1:F1,Region),0))

a2 - =INDEX(Region,MATCH(0,COUNTIF($G$10:G10,Region),0))

b - =INDEX(Product,SMALL(IF(G$1=Region,MATCH(ROW(Region),ROW(Region))),ROW(A1)))

b2 - =INDEX(Product,SMALL(IF($G12=Region,MATCH(ROW(Region),ROW(Region))),COLUMN(A2)))

Product and Region is left intact. I don't change.

=OFFSET('Dependent values'!$H$2,MATCH('Dependent values'!$D2,'Dependent values'!$G$1:$G$7,0)-1,0,0,SUMPRODUCT(--('Dependent values'!$D2=Region)))

to

=OFFSET('Dependent values'!$H$12,MATCH('Dependent values'!$D2,'Dependent values'!$G$11:$G$16,0)-1,0,0,SUMPRODUCT(--('Dependent values'!$D2=Region)))

I have attached the file I use for reference.

https://www85.zippyshare.com/v/llqyWOyy/file.html

Thank you.