• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Holiday Look up

jules meredith

New Member
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

Active Member
@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.
 

Attachments

jules meredith

New Member
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

Active Member
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

New Member
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

Well-Known Member
Adapting the existing formula, in C5 array-enter:
Code:
=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

New Member
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.
 
Top