Forum Discussion
Excel
Hi
I have a Staff rota and I would like to create a list of staff availability based on date for staff who are not on shift or have booked holiday.
I NEED TO SHOW STAFF WHERE CELLS IN COLUMN C are BLANK ............. COLUMNS D TO L ARE BLANK or H .........AND IN NUMERICAL ORDER BASED ON COLUMN B
Date would be inputed in cell B19 and the result displayed underneath
| A | B | C | D | E | F | G | H | I | J | K | L |
| 01/01/2001 | 02/01/2001 | 03/01/2001 | 04/01/2001 | 05/01/2001 | 06/01/2001 | 07/01/2001 | 08/01/2001 | 09/01/2001 | |||
| Person 1 | 12 | W | W | W | W | W | W | W | W | ||
| Person 2 | 45 | epn | D | D | D | D | D | D | D | ||
| Person 3 | 0 | na | |||||||||
| Person 4 | 4 | E | E | E | E | E | E | E | E | E | |
| Person 5 | 10 | NA | H | H | H | H | H | H | H | H | H |
| Person 6 | 0 | H | H | D | D | D | D | H | H | H | |
| Person 7 | 24 | ||||||||||
| Person 8 | 0 | H | H | H | H | ||||||
| Person 9 | 15 | na | R | R | R | R | R | R | R | R | R |
| Person 10 | 12 | R | R | R | R | R | R | R | R | R |
12 Replies
- Let me interpret your requirements this way:
1. You want to know which persons are available on a specific date;
2. A person is available if the entry as of such date is blank, "H", or "R";
3. Given the date in B19, the available persons are listed starting in B20; and
4. The list must be sorted in ascending order according to the corresponding value of the available persons in Column C.
Please comment on my foregoing interpretation.Please go through the excel file hope this will fulfill your criteria.
- please create a sample excel with manual input (indicating your result)
Hopefully attached!!
- what is the purpose of c column, how did you get the number 12, 45, 0 ....
