Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 Contextures http://www.contextures.com/tiptech.html |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#8
![]() |
|||
|
|||
![]()
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:
|
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Data validation dependent lists | Excel Discussion (Misc queries) | |||
dependent lists on data validation | Excel Discussion (Misc queries) | |||
Data Validation and Dependent Lists Q | Excel Worksheet Functions | |||
Data Validation & Dependent Lists | Excel Worksheet Functions | |||
Dependent List- Data Validation | Excel Worksheet Functions |