Dependent drop down lists

Topic

New Reply

I am setting up a worksheet with dependent drop down lists. I set up the first line and it worked fine. when I copied to the second line I received an error message “The Source currently evaluates to an error. Do you want to continue?” YES NO.

WHAT DOES THIS MEAN?

Thank you

Reply | Quote

Replies

Not sure if this is the case but your formula in the second line may be evaluating to an error if you are not employing absolute referencing.

Maud

Reply | Quote

Dear MNN:

Does the below help. These notes were made when I put together dependent drop down.

Create Multiple Drop Down Lists Based On Prior Selection

The real Trick to getting this to work is the excel function INDIRECT()

Example:

Company has 4 Regions – Americas Europe Asia Latin America
Each Region has between 10 to 60 Countries
Each Country has between 1 to 10 Legal Entities

You want the user to first select a Region then Country, and lastly the Legal Entity

Region Country Company

Range 1 Range 2 Range 3
Austria Company 1
x Company 2
x Company 3
x Company 4
x
x
x
Venezuela

For Range 1 – Create a Data Validation rule using the “Data” Ribbon – Data Tools – Data Validation

Under Settings for “Source” Put in “=Regions” where Regions equals a range name with the four regions

For Range 2 – Create a Data Validation rule using the “Data” Ribbon – Data Tools – Data Validation

Under Settings for “Source” Put in “=INDIRECT(Prior Selection Cell)” where prior selection cell equals the exact cell where the previous region name was selected

There must be a predefined range name that exactly matches any Region a user may select. This range will including all the
Countries for a particular Region (Canada, Mexico, Bermuda, Puerto Rico, etc.)

For Range 3 – Create a Data Validation rule using the “Data” Ribbon – Data Tools – Data Validation

Under Settings for “Source” Put in “=INDIRECT(Prior Selection Cell)” where prior selection cell equals the exact cell where the previous Country name was selected

There must be a predefined range name that exactly matches any Country a user may select. This range will including all the Legal Entities
Located in a Country such as “Austria” Range would include (Company 1, Company 2, Company 3, Company 4)

Regards,

Tom D

Reply | Quote

MNN,

Here’s a sample for you to look over. Please note I tried doing this with Dynamic Range Names and the Data Validation dialog would not accept it. However, with standard range names it works just fine.

44968-Dependent-Dropdowns

Sample File: 44969-Dependent-Drop-Down-Lists

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote