Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Dependent Data Validation with Illegal Characters

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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default Dependent Data Validation with Illegal Characters

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Dependent Data Validation with Illegal Characters

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!!! : )

"Debra Dalgleish" wrote:

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 wrote:
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
Contextures
http://www.contextures.com/tiptech.html


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Dependent Data Validation with Illegal Characters

On Jan 12, 11:37 pm, Debra Dalgleish
wrote:
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 wrote:
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
Contextureshttp://www.contextures.com/tiptech.html


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
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Dependent Data Validation with Illegal Characters

On Jan 23, 12:17 am, Debra Dalgleish wrote:
How are the item lists in your workbook set up?

wrote:
On Jan 12, 11:37 pm, Debra Dalgleish
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.


--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html


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
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,979
Default Dependent Data Validation with Illegal Characters

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.

wrote:
On Jan 23, 12:17 am, Debra Dalgleish wrote:

How are the item lists in your workbook set up?

wrote:

On Jan 12, 11:37 pm, Debra Dalgleish
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.


--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html



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
Contextures
http://www.contextures.com/tiptech.html

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Third dependent list data validation with illegal charaters

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:

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#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 wrote:


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

EggHeadCafe - Software Developer Portal of Choice
How to hold a successful meeting
http://www.eggheadcafe.com/tutorials...uccessful.aspx
  #9   Report Post  
Junior Member
 
Posts: 1
Default

I am trying to make a third category based on the 1st & 2nd categories (both of which use illegal characters) and I am having some trouble figuring out the correct data validation source to use.

I tried to copy the same as in the below post using a numberlookup table that has all that has all the possible options derived from the 2nd category. But it does not give me any options under the 3rd category when I do this. I also tried to use the following data validation source:

A2: =level
B2: =INDIRECT(VLOOKUP(A2,namelookup,2,0))
C2: =INDIRECT(SUBSTITUTE((VLOOKUP(A2,namelookup,
2,0))&(VLOOKUP(B2,numberlookup,2,0))," ",""))

The numberlookup being only one set of data corresponding to only one dropdown choice under the 2nd category. It appears to work for this one choice from the 2nd category but I am not sure how I would edit this data validation source to include all sets of data for each option in the 2nd category. Does anyone know how I would do this or if I am going about this wrong? Thanks!


Quote:
Originally Posted by View Post
On Jan 23, 12:17 am, Debra Dalgleish wrote:
How are the item lists in your workbook set up?

wrote:
On Jan 12, 11:37 pm, Debra Dalgleish
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.


--
Debra Dalgleish
Contextureshttp://www.contextures.com/tiptech.html


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
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Dependent Data Validation with Illegal Characters

Debra,

I'm not sure if you're still around on here given this was 7-8 years ago but, I need to use lists with illegal for my work. I tried to mimic your example before I tried to do my work sheet but I couldn't get it to work. Maybe it's because I'm using Excel 2010?

If you could help, that'd be great!

Thank you,
Zack


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Dependent Data Validation with Illegal Characters

Debra,

I'm not sure if you're still around on here given this was 7-8 years
ago but, I need to use lists with illegal for my work. I tried to
mimic your example before I tried to do my work sheet but I couldn't
get it to work. Maybe it's because I'm using Excel 2010?

If you could help, that'd be great!

Thank you,
Zack


I doubt Debra follows this forum, so you'd best contact her thru her
website...

http://www.contextures.com/

...otherwise, you need to provide more info/examples of what you're
trying to do.

Regardless, you must follow naming rules if your list refs are defined
names. The actual list contents (I believe) can have any character you
can type from the keyboard.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data validation dependent lists Negative numbers in brackets Excel Discussion (Misc queries) 4 November 9th 07 07:11 AM
dependent lists on data validation Chris Excel Discussion (Misc queries) 3 November 8th 07 01:03 AM
Data Validation and Dependent Lists Q Sean Excel Worksheet Functions 4 May 19th 07 07:19 PM
Data Validation & Dependent Lists Bob Excel Worksheet Functions 3 December 21st 06 03:38 PM
Dependent List- Data Validation Annie Excel Worksheet Functions 2 September 23rd 05 03:40 PM


All times are GMT +1. The time now is 02:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"