Dependent Data Validation with Illegal Characters

[hjneedshelp]

Hello,
I've read the link: http://www.contextures.com/xlDataVal05.html over and
over, and I am able make a dependent data validation happen for simple
one-word lists.

I do not have one-word lists, however, and the instructions provided for
two-word lists and illegal characters don't dumb things down enough for me to
complete my task - every time I try to follow the instructions, the second
drop-down list (the dependent data) is frozen - nothing drops down.

I have one category list named "Position" (drop-down list is, e.g.: "Field
Sales"; "Distributor/State Manager"; "Sales Manager"), and I want the second
drop-down list to include a list of skills for the "Position" that's
selected. Each skill list (appropriately named for each position) has
listings such as: "Distributor/Broker Relationship"; "Sales Skills";
"Territory, Channel, National Account Management"

I can create the 1st/category drop-down list fine. It's the second one
that's giving me problems. I've tried all the formulas suggested in the
Contextures link (where A2 is the first item in the category/"Position"
range), and I've tried multiple variations of the formulas...all to no avail.


Hope someone can help me. Thank you!

 

[Debra Dalgleish]

I've updated the instructions for the lists with illegal characters.
Maybe this will help:

http://www.contextures.com/xlDataVal02.html#Illegal

Create a lookup table with the range names that you'll use for each
dependent list. For example:

Field Sales FieldSales
Distributor/State Manager DistribMgr
Sales Manager SalesMgr

Then, your SalesMgr list would contain the list of skills for Sales
Managers.

 

[hjneedshelp]

Your updated instructions definitely helped. Thank you!

I also discovered that another feature in my file was goofing things up, so
when I started with a fresher file, it worked great. Again, THANK YOU!!! : )

 

[tom.e.mcgrath]

I've updated the instructions for the lists with illegal characters.
Maybe this will help:

http://www.contextures.com/xlDataVal02.html#Illegal

Create a lookup table with the range names that you'll use for each
dependent list. For example:

Field Sales FieldSales
Distributor/State Manager DistribMgr
Sales Manager SalesMgr

Then, your SalesMgr list would contain the list of skills for Sales
Managers.

Debra,

Can you please explain to me how I can make the Illegal character
formula work for a 3rd category? I want to have the 3rd category
options dependent upon the combination of what is selected for both
categories 1 & 2. Please keep in mind that both categories 1 & 2
contain multiple words with illegal characters.

Thanks,

Tom

 

[Debra Dalgleish]

How are the item lists in your workbook set up?

On Jan 12, 11:37 pm, Debra Dalgleish <[email protected]>
wrote:

Debra,

Can you please explain to me how I can make the Illegal character
formula work for a 3rd category? I want to have the 3rd category
options dependent upon the combination of what is selected for both
categories 1 & 2. Please keep in mind that both categories 1 & 2
contain multiple words with illegal characters.

 

[tom.e.mcgrath]

How are the item lists in your workbook set up?

Hi Debra,

I set everything up as described in the Illegal character section of
this article: http://www.contextures.com/xlDataVal02.html#Illegal.

I created name lookup tables "SymptomNameLookup" and
"GroupNameLookup". The data is extensive, so I had to use multiple
sheets within the same workbook, but it is working, except I cannot
get the list in category 3 to show the options available based on the
combination of selections from the 1st & 2nd category lists.

I'm pretty sure it is a syntax error in the data validation source for
category 3 (cell C2).

This is what I entered for the data validation source:

A2: =Symptom
B2: =INDIRECT(VLOOKUP(A2,SymptomNameLookup,2,0))
C2: =INDIRECT(VLOOKUP(A2,SymptomNameLookup,
2,0))&(VLOOKUP(B2,GroupNameLookup,2,0))

I simplified my workbook and so I could attach a sample file that may
help you understand what I've done, but I don't know how to attach
it. Do you?

Thanks for your help!

Tom

 

[Debra Dalgleish]

Your formula for the third dropdown should work, assuming you have a
range named with the Symptom and Group names, e.g. PainBack, and the
items for the third dropdown are in that range.

 

[vernon miller]

I'm trying to make 3 drop down lists with illegal characters in some of them, contextures has been a great help except my third list will not work properly - the lookup function seems to return the same data for each name that is identical in the second drop down list even tho it is in a different range example : Garage - 12x20 will return the same price as T-111 Cabin Shell - 12x20 . I am trying to make a drop down lists in the following order , Style: , Size: , Price: . Below is a sample of the Data I am entering. Any help would be greatly appreciated, Thanks...

Garage 12x20 $4,785.00
12x24 $5,722.00
12x28 $6,659.00

Gazebo 10x14 Texan $5,295.00
12x16 Texan $6,695.00
9x9 Split Roof $2,995.00

Horse Barn

10x12 w/stall $2,849.00
10x16 w/1 stall $3,495.00
12x28 (2-12x10 stalls w/8' tackroom) $4,195.00

T-111 Cabin Shell
12x20 $4,829.00
12x24 $5,389.00
12x28 $6,119.00




Debra Dalgleish wrote:

Re: Dependent Data Validation with Illegal Characters
12-Jan-08

I've updated the instructions for the lists with illegal characters.
Maybe this will help

http://www.contextures.com/xlDataVal02.html#Illega

Create a lookup table with the range names that you'll use for each
dependent list. For example

Field Sales FieldSale
Distributor/State Manager DistribMg
Sales Manager SalesMg

Then, your SalesMgr list would contain the list of skills for Sales
Managers

hjneedshelp wrote

--
Debra Dalgleis
Contexture
http://www.contextures.com/tiptech.html

EggHeadCafe - Software Developer Portal of Choice
How to hold a successful meeting
http://www.eggheadcafe.com/tutorial...6d-cab220b2f1b9/how-to-hold-a-successful.aspx

 

[UntilTomorrow]

I'm having difficulty trying to get a dependent list off of my primary list, as my primary list consists of two options, 3/4 or 3/8. Help? I've managed to get it work otherwise, except with these two, and I've having difficulty understanding the illegal character section.