Selecting from a row of choices
Posted by Eric on October 18, 2001 7:17 AM
I am looking for suggestions in Excel (Not Access) to allow the user to select from a list of values. The list of values (24) must be entered in a row format. The first selection should be eliminated when the second selection is made. Second selection is eliminated when made....Leaving only one value left that is selectable at the 24th pick.
In normal terms: The person has 24 choices and must rank them from 1 through 24
Any help would be greatly appreciated
Posted by Aladin Akyurek on October 18, 2001 2:12 PM
Eric,
Hope I'm on the right track.
Lets say that C2:C8 the following sample list:
{"carol";"cora";"jeanne";"mary";"mestan";"suzy";"zora"} [ "objects" to be ranked, so to speak ]
In B2 enter: =A2&A3&A4&A5&A6&A7&A8
In D2 enter: =IF(ISNUMBER(SEARCH(C2,$B$2)),0,C2)
Copy down this as far as needed.
In E2 array-enter: =IF(ROW()-ROW($E$2:$E$8)+1>ROWS($D$2:$D$8)-COUNTIF($D$2:$D$8,0),"",INDIRECT(ADDRESS(SMALL((IF($D$2:$D$8<>0,ROW($D$2:$D$8),ROW()+ROWS($D$2:$D$8))),ROW()-ROW($E$2:$E$8)+1),COLUMN($D$2:$D$8))))
Copy down this as far as needed.
In F2 array-enter: =ADDRESS(MIN(IF(LEN(E2:E8)>0,ROW(E2:E8))),COLUMN(E:E))&":"&ADDRESS(MAX(IF(LEN(E2:E8)>0,ROW(E2:E8))),COLUMN(E:E))
Activate A2.
Activate Data|Validation.
Select List for Allow.
Enter as Source:
=INDIRECT($F$2)
While A2 is empty (no selection made yet), copy the formula in it to A3:A8.
Your list is longer, so adjust.
In case you're forgotten: hit CONTROL+SHIFT+ENTER to enter the array formulas.
Aladin
===========
