Dependant lists help needed

[Dando265]

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

 

[Guest]

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

 

[Guest]

See Debra's excellent site:

http://www.contextures.com/xlDataVal13.html

 

[Dando265]

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








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

 

[Guest]

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

 

[Guest]

See http://www.contextures.com/xlDataVal02.html

 

[Guest]

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.

 

[Guest]

Take a look at
http://www.contextures.com/xlDataVal02.html

 

[Guest]

Try what's suggested here and see if it helps.

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

HTH,
Barb Reinhardt

 

[Guest]

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.

 

[Guest]

Hi,

if you post an example it would be more underestandable.

Thanks,

 

[Guest]

try this website:

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

 

[Dando265]

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








- 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

 

[Dando265]

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








- 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

 

[Guest]

Hi, Dan

The lists can be located anywhere in the same workbook.

***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

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

 

[Dando265]

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








- Show quoted text -

Thanks Ron it worked a treat.

Dan