Dependent Drop Down Help, Excel 2010

[Crazyredhead]

Good afternoon,

I have two lists, in two columns in Excel (Column A = "Site Name", Column B = "Address"). Many of the site names are duplicated (multiple addresses for the same site name) and would like to create a dependent drop-down on a separate sheet (Columns J & K if that makes a difference) where if a site name is picked that has multiple addresses, the dependent drop down shows the list of addresses for that particular name that can be picked from, if that makes sense.

For example - column A has the name "Sid's Chicken" listed 20 times (amongst other names, some of which may only be single-outlet establishments), column B has a unique address for each of "Sid's Chicken" shops - I would like to be able to select a specific one of the "Sid's Chicken" shops from the second drop-down when I select "Sid's Chicken" from the first drop down. I can get drop down 1 to work just fine (selecting the "Sid's Chicken", albeit it gives me 20 "Sid's Chicken" to choose from!) but the dependent drop down I just cannot get my head around and am struggling. I've Googled the life out of this and, to be frank, I'm getting more and more confused the more I research this.

I'm using Excel 2010, and any help would be greatly appreciated.

Thank-you in advance.

 

[Akuini]

Hi, @Crazyredhead
For versions of Excel older than Excel 365, this article provides a good method to set up two-tier dependent data validation:
Creating a Dependent Drop Down List in Excel [Step-by-Step Tutorial]
However, depends on your data, this method could be difficult to implement.

I have two lists, in two columns in Excel (Column A = "Site Name", Column B = "Address").

1. About how many unique "Site Name" is in your actual data?
2. Is the data dynamic? I mean do you frequently update your data?

 

[Crazyredhead]

1. About how many unique "Site Name" is in your actual data?
2. Is the data dynamic? I mean do you frequently update your data?

Hi @Akuini

The total data set (at the moment) is around 2,300 site names, of which I'd say around 2,000 are unique - the others are all duplicate site names but individual addresses.
2. The data is constantly being added to as new sites come on board, however, existing data within the sheet is locked and cannot be deleted (addresses can be altered, however).

Hope this helps

 

[Akuini]

The total data set (at the moment) is around 2,300 site names, of which I'd say around 2,000 are unique

With so many unique site names and dynamic data, implementing the method I mentioned earlier can be somewhat challenging. You'll need to create a helper table with thousands of columns. However, of course, it's up to you to choose the best method for your needs.
If you're open to using macros, I have one that can simplify the process of setting up dependent data validation. Let me know if you're interested.

 

[Crazyredhead]

If you're open to using macros, I have one that can simplify the process of setting up dependent data validation. Let me know if you're interested.

I've got no problem with macros. Most of the rest of the functionality is (badly, by me...) written in VBA, anyway!

 

[Akuini]

Ok, try this:
This is an example of 3 level dependent data validation, with a macro to create a helper table.
•You can easily set up more than 3 level dependent data validation as needed.
•You only need 1 main table as the source, 1 helper table (created by macro), 1 named range & 1 UDF.
•The code will generate a non-empty, unique, & sorted data validation list. The list is also dynamic, you can add data on the list without having to change the data validation formula.
•Using the data-validation will not clear the UNDO history, except on the part with automatic deleting to preserve data consistency & when you create or refresh the helper table.
•The columns may or may not be contiguous.

The main functionality of this method is you just need to press Refresh Table button to create a helper table.

The sample workbook & manual:
Akuini - multi dependent data validation,UDF,create helper table - 2

You need to adjust several constant values in this part (in Module1) to suit your actual data setting:

'=============== ADJUST THE CODE IN THIS PART: ================================

Option Explicit

Private Const shDV As String = "Sheet1" 'sheet where data validation is located

Private Const shN As String = "Sheet2" 'sheet where the table as the source of data validation list is located

Private Const sTbl As String = "Table1" 'the table in Sheet2, it is where the list is located

Private Const HC As String = "J1" 'helper column (in sheet2) starts here

'sDT & sDV must be in correct order (in this example 'COUNTRY > CITY > CONTACT).

'You can add more columns as needed.

Private Const sDT As String = "1,2,3" 'Column number on table "Table1": 'COUNTRY > CITY > CONTACT

Public Const sDV As String = "B:B,C:C,D:D" 'Column where data validation is located 'COUNTRY > CITY > CONTACT'==========================================================================