dropdown list help

[muppet77]

is there a way i can have a drop down list to pick items from, that auto updates after every pick so that the new list i pick from has the previous selection omitted?

eg : choose from:
apples
bananas
courgettes
drink

select bananas

new list in next cell:
apples
courgettes
drink

ie there is NO SPACE between the omitted selection (i can get a list but it just leaves a blank space like this:

new list:
apples

courgettes
drink


any ideas?

 

[Aladin Akyurek]

wow thanks!

Now define:

List1 as referring to:

=ListAdmin!$D$9:INDEX(ListAdmin!$D$9:$D$12,MATCH("*",ListAdmin!$D$9:$D$12,-1))

List2 as referring to:

=ListAdmin!$E$9:INDEX(ListAdmin!$E$9:$E$12,MATCH("*",ListAdmin!$E$9:$E$12,-1))

List3 as referring to:

=ListAdmin!$F$9:INDEX(ListAdmin!$F$9:$F$12,MATCH("*",ListAdmin!$F$9:$F$12,-1))

Target

B2 ===> Allow is set to List, Source to List0.
B3 ===> Allow is set to List, Source to List1.
B4 ===> Allow is set to List, Source to List2.
B5 ===> Allow is set to List, Source to List3.

don't understand that bit. what do i have to do? have copied rest into a work sheet.

Lets take:

List1 as referring to:

=ListAdmin!$D$9:INDEX(ListAdmin!$D$9:$D$12,MATCH("*",ListAdmin!$D$9:$D$12,-1))

Activate Insert|Name|Define.
Enter List1 as name in the box for Names in Workbook.
Enter the following in the box for Refers to:

=ListAdmin!$D$9:INDEX(ListAdmin!$D$9:$D$12,MATCH("*",ListAdmin!$D$9:$D$12,-1))

Click OK.

Apply the same procedure for List2 and List3.

I thrust you know how to use these names to set up data validation cells.

 

[muppet77]

ok named all the lists.
for the validation i just put in
list
and the source just List1
list2
list3
list4

into each source box?

 

[Aladin Akyurek]

ok named all the lists.
for the validation i just put in
list
and the source just List1
list2
list3
list4

into each source box?

If you're re-building my example...

B2 must have List0 as source, B3 List1, B4 List2, and B5 List3...

If you are (or anyone else) interested in this formula system for shrinking data validation sublists, just drop your e-mail address via a PM.

 

[just_jon]

ok named all the lists.
for the validation i just put in
list
and the source just List1
list2
list3
list4

into each source box?

=list1
Need the = sign.

 

[muppet77]

cheers fellas for the help.

gonna have play now!

thanks for your time - will reply if any more probs(!)

 

[muppet77]

thanks so much aladin, jon ang yogi et al.
adapted it so it takes 22 items now - mission complete.
ta once again.