Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cascaded validation lists - very dynamic (Ex2003)

    I currently have two columns. The first is constrained by a dynamic validation list. How can I make the validation list for the second column change depending upon the value selected in the first column?

    "All I'd have to do" is make the Source of the validation list refer to the range that is named in cell next to it, but it isn't working how I guessed.

    I created the following dynamic named ranges, with their list contents following in brackets:
    Animal (Fish, Mammal, Insect, Reptile);
    Fish (Trout, Tuna);
    Mammal (Dog, Cat, Cow, Pig, Bat);
    Insect (Ant, Bee, Fly, Gnat);
    Reptile (Crocodile, Snake, Goanna);

    On the sheet with validation applied, column A must be a member of the list Source =Animal. Say the user selects "Fish"
    I then tried to say that the list source for B1 was list Source=A1, hoping that A1 would be resolved to the range named in A1 (Fish). I then tried using Source=Indirect(A1); neither works.

    1. How can I make the source of the second column refer to the range named in the first column, which could be different for every row/cell.
    2. How can this be easily applied to the whole column (something like an array formula?)

    thanks in advance

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: cascaded validation lists - very dynamic (Ex2003)

    See Excel -- Data Validation -- Dependent Lists on Debra Dalgleish's website.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cascaded validation lists - very dynamic (Ex2003)

    Just to add to what Hans said, you want to look at the Using Dynamic Names section near the bottom of that page, which explains that you can't use INDIRECT with a dynamic name and how to work around it.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cascaded validation lists - very dynamic (Ex2003)

    Thanks guys - I've been out of the office for a couple of days. I was going to post a link to the solution we found - but it's the same page recommended by Hans!!

    Thanks again

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: cascaded validation lists - very dynamic (Ex2003)

    I should have added that you can use EVALUATE in another defined name to bypass the limitation of INDIRECT with dynamic defined names.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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