Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Interdependent Data Validation (2003)

    Is it possible to have successive data validation lists tailor to values chosen in previous data validation cells? I have attached a workbook that has a table of corresponding data in A2:C43. A VLOOKUP won't work because the values aren't unique.

    For an example in the attachment, if the North America Region is selected, I want the data validation list to change to only the corresponding North America functions according to A2:C43. After that selection is made, I want the Position Data Validation list to tailor to available values for North America and Function that was chosen.

    Amy
    Attached Files Attached Files

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

    Re: Interdependent Data Validation (2003)


  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Interdependent Data Validation (2003)

    Hi Amy
    You might want to look at how I've set up the Africa sheet, and the named ranges on it, Also the formulae in columns E & F on sheet1. Finally the Data Validation in columns C & D of Sheet1.

    H.T.H.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Interdependent Data Validation (2003)

    Don,
    Thanks for churning this a bit. I follow what you have done. Thank you.

    Were the Europe, Extract and Criteria named ranges attempts to go into diffferent direction with the solution. Should they be disregarded?

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Interdependent Data Validation (2003)

    <hr>Should they be disregarded? <hr>
    Yes. I don't believe they are needed.
    Regards
    Don

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Interdependent Data Validation (2003)

    Combining your approach and tools and tips from the site that Hans recommended, I am running into difficulty accommodating multi-word values and those with other symbols that named range syntax is restricted from accepting. Using the formula SUBSTITUTE(E2," ","") to rid the cell value of spaces so that it can be used as a named range works fine., but how do I add additional substitutes like the "&",""?

    Can there be multiple old_text, new_text values in the formula?

    Amy

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

    Re: Interdependent Data Validation (2003)

    You can't substitute multiple characters in one go, but you can nest functions:
    <code>
    =SUBSTITUTE(SUBSTITUTE(E2," ",""),"&","")
    </code>
    You can nest up to 7 levels deep.

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Interdependent Data Validation (2003)

    Hello Amy
    I also have been considering that problem. The solution lies in yet another named range aliases are stored. The new approach would be along the following lines.

    To develop the validation list for "North America Functions":
    <UL><LI>There would be a range named "N_A_Func" structured like the "AfricaFunction" range in the example I provided earlier..
    <LI>There would be an "Aliases" range of two columns width that contains in one of the rows "North AmericaFunction" and "N_A_Func" in the first and second columns respectively.
    <LI>Cell E4of Sheet1 in the example which I provided would then change to the following formula "=VLOOKUP(B4&"Function",Aliases,2)"[/list]H.T.H.
    Regards
    Don

Posting Permissions

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