• 592K Members
• 9,444 Online
• 718K Conversations

## Excel

Highlighted
Occasional Contributor

# 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
11 Replies

# Re: Excel

please create a sample excel with manual input (indicating your result)
Highlighted

# Re: Excel

Hopefully attached!!

Highlighted

# Re: Excel

what is the purpose of c column, how did you get the number 12, 45, 0 ....
Highlighted

# Re: Excel

Column C is an overtime count in hours.....so it will increase as more overtime is done.

Highlighted

# Re: Excel

why this four person in this list?

 So for 02/01/2001 Person 6 0 Person 8 0 Person 10 12 Person 7 24
Highlighted

# Re: Excel

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

# Re: Excel

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

Highlighted

# Re: Excel

That is correct! Except point 2..........Blank and H only....not R. (my mistake.)

Highlighted

# Re: Excel

I have been trying Index/Match/ aggregate......but I am having trouble with that approach.......

Highlighted

# Re: Excel

Please go through the excel file hope this will fulfill your criteria.

Highlighted

# Re: Excel

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

Related Conversations
copy and past from one sheet to another help
john2tl in Excel on
0 Replies
Hidden External Reference
mtarler in Excel on
1 Replies
VBA To Highlight Rows
TattyJJ in Excel on
8 Replies
Countif Formula returns #Value!
AaronJames in Excel on
1 Replies