Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Hi,
I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
See Debra Dalgleish's coverage of Dependent Lists as her website:
http://www.contextures.com/xlDataVal02.html Does that help? (Post back with more questions) *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
See Debra's excellent site:
http://www.contextures.com/xlDataVal13.html "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
On 5 Jun, 19:42, Ron Coderre
wrote: See Debra Dalgleish's coverage of Dependent Lists as her website:http://www.contextures.com/xlDataVal02.html Does that help? (Post back with more questions) *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265- Hide quoted text - - Show quoted text - I've already looked through the site in question and Mr excel but cannot find reference to my problem. 3 lists dependant on 1 other (4 in total) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Try this:
I'll use only 2 manufacturers to save posting space... With Range Named: Mfg_List in F1:F3 (contains the mfg list: Craftwood, Lockwood, and Basswood) Range Named: Craftwood_Louvre in G1:G10 (contains the Options) Range Named: Craftwood_Frame in H1:H10 (contains the Options) Range Named: Craftwood_Color in I1:I10 (contains the Options) Range Named: Lockwood_Louvre in J1:J10 (contains the Options) Range Named: Lockwood_Frame in K1:K10 (contains the Options) Range Named: Lockwood_Color in L1:L10 (contains the Options) Then....create these list-based DV cells: A1: (contains DV List, based on Mfg_List) B1: (contains DV List, based on =INDIRECT(A1&"_Louvre") C1: (contains DV List, based on =INDIRECT(A1&"_Frame") D1: (contains DV List, based on =INDIRECT(A1&"_Color") Note: as you create each DV ....Excel may warn you that it currently evaluates to an error ....ignore the warning Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
See http://www.contextures.com/xlDataVal02.html
-- Good Luck, Peg "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Hi,
You can use Data, Validation for this. Suppose the first list is contains the items A, B, CC, D and you entered them in A1:A4. Then suppose you want the pick list to be in cell C1. 1. Select C1 and choose the command Data, Validation, and on the first tab under Allows pick List and in the Source box reference enter: =A1:A4, click OK. 2. Suppose when the user picks B from the picklist in cell C1 you want another list to show the values X, Y, Z. Suppose the items for this list are in cells F1:F3. Highlight this range, F1:F3 and name it with the value from the first pick list, in this example B. (Highlight F1:F3 and choose Insert, Name, Define and enter B in the Names in Workbook box. 3. Suppose you want the second pick list to appear in C2. Select C2 and choose Data, Validation, and pick List again. In the Souce box type =INDIRECT(C1). You can create as many sub list as you want using this approach. -- Cheers, Shane Devenshire "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Take a look at
http://www.contextures.com/xlDataVal02.html -- Good Luck, Peg "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Try what's suggested here and see if it helps.
http://www.contextures.com/xlDataVal02.html HTH, Barb Reinhardt "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
The easiest way I know is to type all of your data out. Every possible
option you have then highlight all of that select Data (from menu bar) - then List - then create list. Verify your range and state if you have a header or not. Goodluck. "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Hi,
if you post an example it would be more underestandable. Thanks, -- Farhad Hodjat "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
try this website:
http://www.contextures.com/xlDataVal02.html "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
On 5 Jun, 20:44, Ron Coderre
wrote: Try this: I'll use only 2 manufacturers to save posting space... With Range Named: Mfg_List in F1:F3 (contains the mfg list: Craftwood, Lockwood, and Basswood) Range Named: Craftwood_Louvre in G1:G10 (contains the Options) Range Named: Craftwood_Frame in H1:H10 (contains the Options) Range Named: Craftwood_Color in I1:I10 (contains the Options) Range Named: Lockwood_Louvre in J1:J10 (contains the Options) Range Named: Lockwood_Frame in K1:K10 (contains the Options) Range Named: Lockwood_Color in L1:L10 (contains the Options) Then....create these list-based DV cells: A1: (contains DV List, based on Mfg_List) B1: (contains DV List, based on =INDIRECT(A1&"_Louvre") C1: (contains DV List, based on =INDIRECT(A1&"_Frame") D1: (contains DV List, based on =INDIRECT(A1&"_Color") Note: as you create each DV ...Excel may warn you that it currently evaluates to an error ...ignore the warning Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265- Hide quoted text - - Show quoted text - I think I may be able to do something with your suggestion. Just one thing all my lists are on another sheet in the same workbook does that make any difference? or do the all have to be on the same sheet. Thanks Dan |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
On 5 Jun, 23:23, ShaneDevenshire
wrote: Hi, You can use Data, Validation for this. Suppose the first list is contains the items A, B, CC, D and you entered them in A1:A4. Then suppose you want the pick list to be in cell C1. 1. Select C1 and choose the command Data, Validation, and on the first tab under Allows pick List and in the Source box reference enter: =A1:A4, click OK. 2. Suppose when the user picks B from the picklist in cell C1 you want another list to show the values X, Y, Z. Suppose the items for this list are in cells F1:F3. Highlight this range, F1:F3 and name it with the value from the first pick list, in this example B. (Highlight F1:F3 and choose Insert, Name, Define and enter B in the Names in Workbook box. 3. Suppose you want the second pick list to appear in C2. Select C2 and choose Data, Validation, and pick List again. In the Souce box type =INDIRECT(C1). You can create as many sub list as you want using this approach. -- Cheers, Shane Devenshire "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265- Hide quoted text - - Show quoted text - I know how to do the two lists, please explain what to do for the third and fourth lists, thats where i'm coming unstuck Thanks Dan |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Hi, Dan
The lists can be located anywhere in the same workbook. *********** Regards, Ron XL2002, WinXP "Dando265" wrote: On 5 Jun, 20:44, Ron Coderre wrote: Try this: I'll use only 2 manufacturers to save posting space... With Range Named: Mfg_List in F1:F3 (contains the mfg list: Craftwood, Lockwood, and Basswood) Range Named: Craftwood_Louvre in G1:G10 (contains the Options) Range Named: Craftwood_Frame in H1:H10 (contains the Options) Range Named: Craftwood_Color in I1:I10 (contains the Options) Range Named: Lockwood_Louvre in J1:J10 (contains the Options) Range Named: Lockwood_Frame in K1:K10 (contains the Options) Range Named: Lockwood_Color in L1:L10 (contains the Options) Then....create these list-based DV cells: A1: (contains DV List, based on Mfg_List) B1: (contains DV List, based on =INDIRECT(A1&"_Louvre") C1: (contains DV List, based on =INDIRECT(A1&"_Frame") D1: (contains DV List, based on =INDIRECT(A1&"_Color") Note: as you create each DV ...Excel may warn you that it currently evaluates to an error ...ignore the warning Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265- Hide quoted text - - Show quoted text - I think I may be able to do something with your suggestion. Just one thing all my lists are on another sheet in the same workbook does that make any difference? or do the all have to be on the same sheet. Thanks Dan |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
Clarification:
The lists can be located on other sheets in the workbook ONLY if they are referenced via Range Names, otherwise they must be on the same sheet as the Data Validation cells. *********** Regards, Ron XL2002, WinXP "Ron Coderre" wrote: Hi, Dan The lists can be located anywhere in the same workbook. *********** Regards, Ron XL2002, WinXP "Dando265" wrote: On 5 Jun, 20:44, Ron Coderre wrote: Try this: I'll use only 2 manufacturers to save posting space... With Range Named: Mfg_List in F1:F3 (contains the mfg list: Craftwood, Lockwood, and Basswood) Range Named: Craftwood_Louvre in G1:G10 (contains the Options) Range Named: Craftwood_Frame in H1:H10 (contains the Options) Range Named: Craftwood_Color in I1:I10 (contains the Options) Range Named: Lockwood_Louvre in J1:J10 (contains the Options) Range Named: Lockwood_Frame in K1:K10 (contains the Options) Range Named: Lockwood_Color in L1:L10 (contains the Options) Then....create these list-based DV cells: A1: (contains DV List, based on Mfg_List) B1: (contains DV List, based on =INDIRECT(A1&"_Louvre") C1: (contains DV List, based on =INDIRECT(A1&"_Frame") D1: (contains DV List, based on =INDIRECT(A1&"_Color") Note: as you create each DV ...Excel may warn you that it currently evaluates to an error ...ignore the warning Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265- Hide quoted text - - Show quoted text - I think I may be able to do something with your suggestion. Just one thing all my lists are on another sheet in the same workbook does that make any difference? or do the all have to be on the same sheet. Thanks Dan |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dependant lists help needed
On 5 Jun, 20:44, Ron Coderre
wrote: Try this: I'll use only 2 manufacturers to save posting space... With Range Named: Mfg_List in F1:F3 (contains the mfg list: Craftwood, Lockwood, and Basswood) Range Named: Craftwood_Louvre in G1:G10 (contains the Options) Range Named: Craftwood_Frame in H1:H10 (contains the Options) Range Named: Craftwood_Color in I1:I10 (contains the Options) Range Named: Lockwood_Louvre in J1:J10 (contains the Options) Range Named: Lockwood_Frame in K1:K10 (contains the Options) Range Named: Lockwood_Color in L1:L10 (contains the Options) Then....create these list-based DV cells: A1: (contains DV List, based on Mfg_List) B1: (contains DV List, based on =INDIRECT(A1&"_Louvre") C1: (contains DV List, based on =INDIRECT(A1&"_Frame") D1: (contains DV List, based on =INDIRECT(A1&"_Color") Note: as you create each DV ...Excel may warn you that it currently evaluates to an error ...ignore the warning Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Dando265" wrote: Hi, I'm stuck trying to create three dependant lists from a selction of another list. My Products are shutters made from different materials,(1st list) Craftwood, Lockwood, and Basswood. Each of these products have different louvre options (1st dependant on list1) They also have diffent frame options (2nd list dependant on list1) And finally they have different colours (3rd list dependant on list1) Can anyone help me please, i'm really struggling with this. Thanks Dando265- Hide quoted text - - Show quoted text - Thanks Ron it worked a treat. Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dependant Lists Question | Excel Worksheet Functions | |||
3 way dependant lists | Excel Worksheet Functions | |||
Dependant Lists in Excel | Excel Worksheet Functions | |||
dependant combobox list | New Users to Excel | |||
Dependant Lists | Excel Discussion (Misc queries) |