DropLists not excluding prior selections

"StraightList" sheet on attached works perfectly. Any name selected in any DropList in F2 - F5 is removed from the other lists, and restores it if the name is cancelled.

"Two Column List" sheet should do the same even through the DropLists are now in two columns (because they are still accessing the same common Range), but does not work.

Whatever name is entered in any of the DropBoxes, the formulae removes it from the "pool" of future choices, but not from the DropLists themselves.

Any advice, solutions or alternative approaches welcomed as ever,


Ochimus

I Created a dynamic named range "ROOM_MATES" in column K of "Two Column List":

in K2

=IFERROR(INDEX($J$2:$J$21,SMALL(IF($I$2:$I$21<>"",ROW($J$2:$J$21)-ROW($J$2)+1,""),ROWS($J$2:J2))),"")

Enter with Ctrl+Shift+Enter

Copy down

Changed DV to use "ROOM_MATES"

same result no matrix formula


Kind regards
Leo

Gentlemen,

You've demonstrated once again why this Forum is invaluable,

Getting to the point where satisfying some clients' Excel demands requires a Masters in Applied Logic and Advanced calculus!

Have a well-earned weekend.

Ochimus