May 13 2019 02:19 AM
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 |
May 13 2019 02:30 AM
May 13 2019 02:54 AM
May 13 2019 03:00 AM
Column C is an overtime count in hours.....so it will increase as more overtime is done.
May 13 2019 03:07 AM
why this four person in this list?
So for | |
02/01/2001 | |
Person 6 | 0 |
Person 8 | 0 |
Person 10 | 12 |
Person 7 | 24 |
May 13 2019 03:14 AM
May 13 2019 03:18 AM
Good point...there should only be 3 people in that list!
Person 10 should not be there!!
I'd input the date in B19 and the staff who have the lowest count from column C would appear when column D is blank and the corresponding column under the entered date is blank or contains H
May 13 2019 05:00 AM
I have been trying Index/Match/ aggregate......but I am having trouble with that approach.......
May 13 2019 06:01 AM
Please go through the excel file hope this will fulfill your criteria.
May 13 2019 06:33 AM
That is amazing but not quite as I need it.......
If Column D contains N/A and Columns E to M are Blank or H then that person is Not Available
Also as columns D to M are updated I need to see the list update accordingly
Aug 02 2020 07:22 PM
@simont485 , I know that this is an old thread, but the new Excel Dynamic Arrays make the solution easy. If you have the latest update of Excel 365, this should work as you asked.