Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
I have a table containing Names and Class Days:
Joe Monday Sue Tuesday Bob Tuesday Sam Monday Bill Wednesday Jon Thursday I want to create a list to show Names per Class Day: Monday Tuesday Wednesday Thursday Joe Sue Bill Jon Sam Bob |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
Say input is in A2:B11. Say your output starts from F1 (Monday)
through L1 and employee names start at F2. Then at F2: Simple version (produces #NUM! when no more employees) =INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=F$1,ROW($B$2 :$B$11)-ROW($B $2)+1),ROW()-ROW($F$1))) Error checking (leaves cell blank if no more employees) =IF(ISERROR(SMALL(IF($B$2:$B$11=F$1,ROW($B$2:$B$11 )-ROW($B$2)+1),ROW()- ROW($F$1))),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$1 1=F$1,ROW($B$2:$B $11)-ROW($B$2)+1),ROW()-ROW($F$1)))) Either formula is an *array* formula. Commit with Shift+Ctrl+Enter. You can copy the formula down and across. HTH Kostis Vezerides On Apr 21, 7:41 pm, Dallas PM wrote: I have a table containing Names and Class Days: Joe Monday Sue Tuesday Bob Tuesday Sam Monday Bill Wednesday Jon Thursday I want to create a list to show Names per Class Day: Monday Tuesday Wednesday Thursday Joe Sue Bill Jon Sam Bob |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
I can see the results when I "walk thru" the formula in the background, but
when I do SHIFT+CTRL+ENTER, no values are stored. I am running Excel 2003 "vezerid" wrote: Say input is in A2:B11. Say your output starts from F1 (Monday) through L1 and employee names start at F2. Then at F2: Simple version (produces #NUM! when no more employees) =INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=F$1,ROW($B$2 :$B$11)-ROW($B $2)+1),ROW()-ROW($F$1))) Error checking (leaves cell blank if no more employees) =IF(ISERROR(SMALL(IF($B$2:$B$11=F$1,ROW($B$2:$B$11 )-ROW($B$2)+1),ROW()- ROW($F$1))),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$1 1=F$1,ROW($B$2:$B $11)-ROW($B$2)+1),ROW()-ROW($F$1)))) Either formula is an *array* formula. Commit with Shift+Ctrl+Enter. You can copy the formula down and across. HTH Kostis Vezerides On Apr 21, 7:41 pm, Dallas PM wrote: I have a table containing Names and Class Days: Joe Monday Sue Tuesday Bob Tuesday Sam Monday Bill Wednesday Jon Thursday I want to create a list to show Names per Class Day: Monday Tuesday Wednesday Thursday Joe Sue Bill Jon Sam Bob |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Drop Down List Question
I don;t understand this last post, what you mean by "walking through".
If your F2 (or whichever cell you chose) displays the formula, select the cell, double-click to enter Edit mode, and then instead of Enter use Shift+Ctrl+Enter. Does this help? Kostis On Apr 21, 9:27 pm, Dallas PM wrote: I can see the results when I "walk thru" the formula in the background, but when I do SHIFT+CTRL+ENTER, no values are stored. I am running Excel 2003 "vezerid" wrote: Say input is in A2:B11. Say your output starts from F1 (Monday) through L1 and employee names start at F2. Then at F2: Simple version (produces #NUM! when no more employees) =INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=F$1,ROW($B$2 :$B$11)-ROW($B $2)+1),ROW()-ROW($F$1))) Error checking (leaves cell blank if no more employees) =IF(ISERROR(SMALL(IF($B$2:$B$11=F$1,ROW($B$2:$B$11 )-ROW($B$2)+1),ROW()- ROW($F$1))),"",INDEX($A$2:$A$11,SMALL(IF($B$2:$B$1 1=F$1,ROW($B$2:$B $11)-ROW($B$2)+1),ROW()-ROW($F$1)))) Either formula is an *array* formula. Commit with Shift+Ctrl+Enter. You can copy the formula down and across. HTH Kostis Vezerides On Apr 21, 7:41 pm, Dallas PM wrote: I have a table containing Names and Class Days: Joe Monday Sue Tuesday Bob Tuesday Sam Monday Bill Wednesday Jon Thursday I want to create a list to show Names per Class Day: Monday Tuesday Wednesday Thursday Joe Sue Bill Jon Sam Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down List Question | Excel Discussion (Misc queries) | |||
Drop Down List Question | Excel Discussion (Misc queries) | |||
Drop down list question | New Users to Excel | |||
drop down list question | Excel Discussion (Misc queries) | |||
Drop down list question... | Excel Worksheet Functions |