Interdependent Data Validation (2003)

Topic

New Reply

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

Reply | Quote

Replies

See Excel — Data Validation — Dependent Lists.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

[indent]

---

Should they be disregarded?

---

[/indent]
Yes. I don’t believe they are needed.

Reply | Quote

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

Reply | Quote

You can’t substitute multiple characters in one go, but you can nest functions:

=SUBSTITUTE(SUBSTITUTE(E2," ",""),"&","")

You can nest up to 7 levels deep.

Reply | Quote

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”:

• There would be a range named “N_A_Func” structured like the “AfricaFunction” range in the example I provided earlier..
• 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.
• 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.

Reply | Quote