Home |
Search |
Today's Posts |
#1
|
|||
|
|||
IF function?
I have created an excel work schedule, it contains Columns Mon - Fri and Rows
Employee 1 - 20. Using validation, i have created a list where only the codes Off, On, Sick, Out, and In can be used. I want to add a row to the bottom of my sheet that will tell me which employee is "out". This way, instead of searching through the whole report, I can look at that row for each day and it will display the name of whoever is "out". |
#2
|
|||
|
|||
One quick and simple way is to use autofilter on the top row where you have
the day headers: Mon, Tues ... Fri Assume the day headers are in the top row, say in B1:F1, (with Employee names assumed in col A, in A2 down) Select the range, click Data Filter Autofilter Now you could just select: Out from the autofilter droplist in say, B1 (for Mon) and the filtered rows in col A (Employee names) will give you the desired results -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "YanYan" wrote in message ... I have created an excel work schedule, it contains Columns Mon - Fri and Rows Employee 1 - 20. Using validation, i have created a list where only the codes Off, On, Sick, Out, and In can be used. I want to add a row to the bottom of my sheet that will tell me which employee is "out". This way, instead of searching through the whole report, I can look at that row for each day and it will display the name of whoever is "out". |
#3
|
|||
|
|||
Say your data list is in A1 to F21, with headers, Mon. thru Fri. in B1 to
F1. A2 to A21 is employee number. In B22 to F22 enter another row formatted to your validation list. You can choose any one of the validation codes, and this formula will return a list of employee numbers that match the code you picked. In B23 enter this *array* formula: =IF(B$22="","",IF(ISERR(SMALL(IF(B$2:B$21=B$22,ROW ($1:$20)),ROW(A1))),"",IND EX($A$2:$A$21,SMALL(IF(B$2:B$21=B$22,ROW($1:$20)), ROW(A1))))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. NOW, copy this formula across to F23. Then, select B23 to F23, and copy down for as many rows as you might anticipate the numbers of employees that would meet the code you picked in Row 22. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "YanYan" wrote in message ... I have created an excel work schedule, it contains Columns Mon - Fri and Rows Employee 1 - 20. Using validation, i have created a list where only the codes Off, On, Sick, Out, and In can be used. I want to add a row to the bottom of my sheet that will tell me which employee is "out". This way, instead of searching through the whole report, I can look at that row for each day and it will display the name of whoever is "out". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions |