Create dependent drop down lists containing unique distinct values in excel
Here is a list of order numbers and products.
We are going to create two drop down lists.
The first drop down list contains unique distinct values from column A.
The second drop down list contains unique distinct values from column B, based on chosen value in the first drop down list.
Create a dynamic named range
- Click “Formulas” tab
- Click “Name Manager”
- Click “New…”
- Type a name. I named it “order”. (See attached file at the end of this post)
- Type =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000)) in “Refers to:” field.
- Click “Close” button
Create a unique distinct list from column A
- Select Sheet2
- Select cell A2
- Type “=INDEX(order,MATCH(0,COUNTIF($A$1:A1,order),0))” + CTRL + SHIFT + ENTER
- Copy cell A2 and paste it down as far as needed.
Create a dynamic named range to get unique distinct list
- Select Sheet2
- Click “Formulas” tab
- Click “Name Manager”
- Click “New…”
- Type a name. I named it “uniqueorder”. (See attached file at the end of this post)
- Type =OFFSET(Sheet2!$A$2, 0, 0, COUNT(IF(Sheet2!$A$2:$A$1000=””, “”, 1)), 1) in “Refers to:” field.
- Click “Close” button
Create drop down list
- Select Sheet1
- Select cell D2
- Click Data tab
- Click Data validation button
- Click “Data validation…”
- Select List in the “Allow:” window.
- Type =uniqueorder in the “Source:” window
- Click OK!
Here is a picture of what we have accomplished so far.
How to create a secondary unique list based on only one chosen cell value in first drop down list
Create a dynamic named range
- Click “Formulas” tab
- Click “Name Manager”
- Click “New…”
- Type a name. I named it “product”. (See attached file at the end of this post)
- Type =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$1000)) in “Refers to:” field.
- Click “Close” button
Create a unique distinct list from column B
- Select Sheet2
- Select cell B2
- Type “=INDEX(product, MATCH(0, COUNTIF($B$1:B1, product)+(order<>Sheet1!$D$2), 0))” + CTRL + SHIFT + ENTER
- Copy cell B2 and paste it down as far as needed.
Create a dynamic named range to get unique distinct list
- Select Sheet2
- Click “Formulas” tab
- Click “Name Manager”
- Click “New…”
- Type a name. I named it “uniqueproduct”. (See attached file at the end of this post)
- Type =OFFSET(Sheet2!$B$2, 0, 0, COUNT(IF(Sheet2!$B$2:$B$1000=””, “”, 1)), 1) in “Refers to:” field.
- Click “Close” button
Create drop down list
- Select Sheet1
- Select cell D5
- Click Data tab
- Click Data validation button
- Click “Data validation…”
- Select List in the “Allow:” window.
- Type =uniqueproduct in the “Source:” window
- Click OK!
Download example workbook
unique-distinct-dependent-lists.xls (Excel 97-2003 Workbook *.xls)
Download example workbook with a third column of data
unique-distinct-dependent-lists1-three-columns.xls (Excel 97-2003 Workbook *.xls)
Courtesy: get digital help
Thanks for this Mr. Raghu, very helpful
Hi Raghu, I need help with creating a unique data validation list and then creating a unique dependent data validation list using the first drop down list?