Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dependent drop down lists

    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

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    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.

    Dependent Dropdowns.PNG

    Sample File: Dependent Drop Down Lists.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •