muppet77 said:
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?...
I have no idea how what follows compares to the method the link quoted by Brian...
Problem statement. Given an alphabetized list,
List0, set up a data validation cell where
List0 is
the source, say cell B2 on sheet
Target. Set up a second data validation cell, in B3, on Target where the alphabetized
List1 is the source with the proviso that
List1 must consist of
List0 minus whatever is selected in B2. Set up a third data validation cell in B4, on
Target where alphabetized
List2 is the source with the proviso that
List2 must consist of
List0 minus the selections made in B2 and B3. And so on.
ListAdmin
Just as in my previous post, the source data is audited for distinctness. This bit can, if so desired, be
dropped for efficiency.
Formulas...
A7:
=--(COUNTA(A9:A12)=SUMPRODUCT((A9:A12<>"")/COUNTIF(A9:A12,A9:A12&"")))
B6:
=IF(N(A7),MATCH(REPT("z",255),A:A),"")
B7:
=IF(N(B6),B6-(CELL("Row",A9)-1),"")
B9, which is copied down:
=IF((A9<>"")*N($A$7),SUMPRODUCT((A9>OFFSET($A$9,0,0,$B$7,1))+0)+1,"")
C8, which is copied across...
=INDEX(Target!$B$2:$B$5,COLUMN()-COLUMN($C$8)+1)
This records the selections that are made on
Target.
C9, which is copied down...
=IF(ROW()-ROW($C$9)+1<=$B$7,INDEX($A$9:$A$12,MATCH(ROW()-ROW($C$9)+1,$B$9:$B$12,0)),"")
Now define
List0 as referring to:
=ListAdmin!$C$9:$C$12
D2,which is copied across & down to row 5...
=IF(ISNUMBER(MATCH($A9,$C$8:C$8,0)),"",VLOOKUP($A9,$A$9:$B$12,2,0))
D9, which is copied accross & down...
=IF(ROW()-ROW(D$9)+1<=COUNT(D$2:D$5),INDEX($A$9:$A$12,MATCH(SMALL(D$2:D$5,ROW()-ROW(D$9)+1),$B$9:$B$12,0)),"")
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.