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".

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".

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".