Dependant Lists??

[a2fire2i]

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.

 

[HalfAce]

I'm not sure if this will help, but judging by your last question ("Could i make b1 dependant on c1?") I think it will.

Have a look at these links and see if they help at all.
I use this method for dependant lists from several columns. (again, providing I understand what you're looking for.)

http://www.mrexcel.com/board2/viewtopic.php?t=53213

http://www.mrexcel.com/board2/viewtopic.php?p=482686

 

[a2fire2i]

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]

Book2
	A	B	C	D	E	F	G	H
1	List1	Choice2	Choiceb	ColA	List1B	List1C	List2B	List2C
2				List1	Choice1	Choicea
3				List2	Choice2	Choiceb
4				List3	Choice3	Choicec
5					Choice4	Choicex
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]

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]

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]

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

 

[just_jon]

Book3
	A	B	C	D	E	F	G	H
1	List1	Choice2	Choiceb	ColA	List1B	List1C	List2B	List2C
2				List1	Choice1	Choicea	Blah1	Blah2
3				List2	Choice2	Choiceb
4				List3	Choice3	Choicec
5					Choice4	Choicex
6
7			Named Ranges		ColA
8					List1B
9					List1C
10					List2B
11					List2C
12			Data Validation
13			Cell	Source
14			A1	=ColA
15			B1	=INDIRECT($A$1&"B")
16			C1	=INDIRECT($A$1&"C")
17
Sheet1

Would a demo workbook be helpful?

 

[a2fire2i]

Book1.xls
	A	B	C	D	E	F	G	H	I	J
1	ERC	DE	N	erc	der	debill	decost	nrun	nbil	ncost
2				r	der1	debill1	decost1	nrun1	nbill1	ncost1
3				b	der2	debill2	decost2	nrun2	nbill2	ncost2
4				c	der2	debill3	decost3	nrun3	nbill3	ncost3
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]

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.