Holiday Look up

[jules meredith]

I have a spreadsheet (inherited) that displays the booked holiday for over 70 staff.

The spreadsheet is structured with a list employees names in column A (1 for each row). Each subsequent column represents a day - (Monday 2nd Jan to Friday 29th December). Each 5 days are grouped together as "week 1", "week 2"....... "week 52" (no one works Saturday of Sunday).

If someone has booked holiday an "H" is inserted in the cell that intersects the date booked and their name. Other codes are also used to signify absence. i.e W = Time in Lieu, S = Sickness etc.

Is there a way I can enter a week number i.e. 37 and for excel to return a list of people who have booked holiday for that week e.g.



week 37 Mon Tues Wed Thur Fri

Bob Bob Bob Bob Bob

Fred Carol Peter Peter

Carol

I can create a pivot table to return the information I need for a single day but I don't know how to create a dynamic range without having 260 pivot tables, one for each day and scrolling to the appropriate date range.

 

[r2c2]

@jules meredith

Interesting problem. You can use mashup of INDEX, MATCH, SMALL to get the output. See attached workbook. Feel free to customize it based on the actual shape of data you have. I have used a few named ranges to keep the formulas tidy. You may remove them and replace with either cell refs or some other.

 

[p45cal]

Post a file for us to play with.

 

[jules meredith]

Your a genius R2C2 that is exactly what I needed and i would not need to edit the existing spreadsheet too much. Also with a simple tweak you could create new tables to see the sickness, time in lieu etc. Brilliant thank you so much.

 

[r2c2]

Your a genius R2C2 that is exactly what I needed and i would not need to edit the existing spreadsheet too much. Also with a simple tweak you could create new tables to see the sickness, time in lieu etc. Brilliant thank you so much.

Thanks Jules

 

[jules meredith]

Hi all & - R2C2 -

A couple of years down the road and I need to tweak the solution/formulas (r2c2) kindly offered.

Using the same system as outlined originally and utilising the formulas and named ranges as detailed in the file "look-who-is-away.xlsx" I would like to be able to pull a list of all people with either one of two bits of information. Specifically those cells that are blank "" (at Work) and any cells which have an "R" (at work but reduced hours).

I have tried adding an OR() to the original formula (Look-who-is-away.xlsx")

{=IFERROR(INDEX(names,SMALL(IF(INDEX(cal,,$D$2+COLUMNS($C$4:F$4)-1)="H",nums),Lookup!$B5)),"")}

but either Excel gives me an error message or I don't get the results I would expect.

eg - {=IFERROR(INDEX(names,SMALL(IF(INDEX(cal,,$D$2+COLUMNS($C$4:F$4)-1)=OR("","R"),nums),Lookup!$B5)),"")}
returns empty cells (no names)

& -
{=IFERROR(INDEX(names,SMALL(IF(OR(INDEX(cal,,$D$2+COLUMNS($C$4:G$4)-1)=""),(INDEX(cal,,$D$2+COLUMNS($C$4:G$4)-1)="R"),nums),Lookup!$B5)),"")}
returns a mixture of names and blank cells including cells that don't have "" or "R"

Thank you in advance for any advice or help on this problem.

 

[p45cal]

Adapting the existing formula, in C5 array-enter:

=IFERROR(INDEX(names,SMALL(IF((INDEX(cal,,$D$2+COLUMNS($C$4:C$4)-1)="")+(INDEX(cal,,$D$2+COLUMNS($C$4:C$4)-1)="R")>0,nums),$B5)),"")

 

[jules meredith]

Wow - thanks p45cal - that is brilliant. I was convinced the solution involved OR() so would not have gotten this in a month of Sundays. I really appreciate your help on this.