Dependent Data Validation with Illegal Characters

  • Thread starter Thread starter hjneedshelp
  • Start date Start date
H

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!
 
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.
 
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!!! : )
 
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.
Click to expand...

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
 
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.
Click to expand...
 
How are the item lists in your workbook set up?
Click to expand...

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
 
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.
 
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
 
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.
 
Back
Top