Holiday lookup!

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


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 to display each day and scrolling to the appropriate date range. 


By: Jules Meredith

Leave a Reply

Your email address will not be published. Required fields are marked *