Dependant Lists??

a2fire2i

New Member
Joined
Nov 14, 2005
Messages
8
Ok, i know dependant list works for 2 colums, but will it work for 3 or more?

This is what i need.

A1 will be a pick list that has the values r, p, c, b, and cw

b1 will look like this
if A1=r, show list der (this will be another pick list with data)
if A1=p, show list dep (this will be another pick list with data)
if A1=c, show list dec (this will be another pick list with data)
if A1=b, show list deb (this will be another pick list with data)
if A1=cw show list decw (this will be another pick list with data)

c1 will look like this:
if A1=r, show list nr (this will be another pick list with data)
if A1=p, show list np (this will be another pick list with data)
if A1=c, show list nc (this will be another pick list with data)
if A1=b, show list nb (this will be another pick list with data)
if A1=cw show list ncw (this will be another pick list with data)


Is there a way to do this? Or any easier way to design this? Could i make b1 dependat on c1? There are several items that would go in each of the pick list, so i figued this wasnt the way to go? The List are located on another work sheet, within the same file. Any ideas??


Thanks for the help.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

a2fire2i

New Member
Joined
Nov 14, 2005
Messages
8
How do you make it dependant for several columns? I want to make b1 dependant on a1, and c1 dependant on a1. How would i do that since i wont be able to name both of those tables the same name??
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Book2
ABCDEFGH
1List1Choice2ChoicebColAList1BList1CList2BList2C
2List1Choice1Choicea
3List2Choice2Choiceb
4List3Choice3Choicec
5Choice4Choicex
6
7
Sheet3


D2:D3 named range ColA
E2:E4 named range List1B
F2:F4 named range List1C

A1 validated as List | =ColA
B1 validated as List | =INDIRECT($A$1&"B")
C1 validated as List | =INDIRECT($A$1&"C")

And so on ...
 

a2fire2i

New Member
Joined
Nov 14, 2005
Messages
8

ADVERTISEMENT

I am not seeing how this would work. I set it up like that, and it allows me to pick anything in list1b, no matter what is selected in a1...
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
a2fire2i said:
I am not seeing how this would work. I set it up like that, and it allows me to pick anything in list1b, no matter what is selected in a1...

If you've set up the named ranges and validation, using in-cell drop-downs and making it style=Stop, then on input of List1 in A1, cell B1's validation should resolve to

=List1B

and allow only those 4 selections.
 

a2fire2i

New Member
Joined
Nov 14, 2005
Messages
8

ADVERTISEMENT

Its set up that way, but did not work that way for me..
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Book3
ABCDEFGH
1List1Choice2ChoicebColAList1BList1CList2BList2C
2List1Choice1ChoiceaBlah1Blah2
3List2Choice2Choiceb
4List3Choice3Choicec
5Choice4Choicex
6
7Named RangesColA
8List1B
9List1C
10List2B
11List2C
12Data Validation
13CellSource
14A1=ColA
15B1=INDIRECT($A$1&"B")
16C1=INDIRECT($A$1&"C")
17
Sheet1


Would a demo workbook be helpful?
 

a2fire2i

New Member
Joined
Nov 14, 2005
Messages
8
Book1.xls
ABCDEFGHIJ
1ERCDENercderdebilldecostnrunnbilncost
2rder1debill1decost1nrun1nbill1ncost1
3bder2debill2decost2nrun2nbill2ncost2
4cder2debill3decost3nrun3nbill3ncost3
Sheet1


Please pardon my &nbsp:'s

Heres the deal:
In A2 they will get a pick list to select something from the erc column.

In B2 they will have to pick from the list from colums e-g, depending on what they selected in A1. IE: If they selected R, they would have to chose from the der column. If they selected B, they would have to chose from the debill column. If they selected C, they will chose something from the decost column.

In C2 they will have to pick from the list from colums h-j, depending on what they selected in A1. IE: If they selected R, they would have to chose from the nrun column. If they selected B, they would have to chose from the nbill column. If they selected C, they will chose something from the ncost column.
 

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
The thing is, to get this kind of dependent lists to work, you must structure your named ranges so that prior selections can be translated into the now-required named range{s}.

Take a good look at the structure of my named range names in the last exhibit I posted above.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,534
Messages
5,572,743
Members
412,482
Latest member
arooshrana2
Top