Good morning I wonder if anyone can point me in the right direction I have a holiday tracker that I wish to create a lookup sheet so i enter a name and get the dates returned that they have booked.
The sheet contains a list of names in one column so i would want to match that then go along the row of that name for a few defined values (Holiday, ServiceDay) and display the date that is contained in row 7
I have included a sample sheet if this helps in my explination any better
The sheet contains a list of names in one column so i would want to match that then go along the row of that name for a few defined values (Holiday, ServiceDay) and display the date that is contained in row 7
I have included a sample sheet if this helps in my explination any better
Test Holiday Tracker.xlsx | ||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | N | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | |||||||||||||
2 | 2021 Holiday Tracker | Left | TOTAL PER DAY: | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 1 | 1 | 0 | |||||||||||||||||
3 | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | #REF! | ||||||||||||||||
4 | Total Days Remaining: 242 | |||||||||||||||||||||||||||||
5 | ||||||||||||||||||||||||||||||
6 | Sat | Sun | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Mon | Tue | Wed | Thu | Fri | ||||||||||||||||
7 | Area | Role | Name | 02/01 | 03/01 | 04/01 | 05/01 | 06/01 | 07/01 | 08/01 | 09/01 | 10/01 | 11/01 | 12/01 | 13/01 | 14/01 | 15/01 | |||||||||||||
8 | G2S | Supervisor | Name 1 | 3 | ||||||||||||||||||||||||||
9 | G2S | Teamleader | Name 2 | 5 | ||||||||||||||||||||||||||
10 | G2S | Associate 01 | Name 3 | 6 | ||||||||||||||||||||||||||
11 | G2S | Associate 02 | Name 4 | 6 | AA | |||||||||||||||||||||||||
12 | G2S | Associate 03 | Name 5 | 7 | AA | |||||||||||||||||||||||||
13 | G2S | Associate 04 | Name 6 | 9 | ||||||||||||||||||||||||||
14 | G2S | Associate 05 | Name 7 | 10 | ||||||||||||||||||||||||||
15 | G2S | Associate 06 | Name 8 | 11 | ||||||||||||||||||||||||||
16 | G2S | Associate 07 | Name 9 | 11 | FH | |||||||||||||||||||||||||
17 | G2S | Associate 08 | Name 10 | 13 | ||||||||||||||||||||||||||
18 | G2S | Associate 09 | Name 11 | 14 | ||||||||||||||||||||||||||
19 | G2S | Associate 10 | Name 12 | 14 | SERVICE | |||||||||||||||||||||||||
20 | G2S | Associate 11 | Name 13 | 16 | DENIED | |||||||||||||||||||||||||
21 | G2S | Associate 12 | Name 14 | 17 | ||||||||||||||||||||||||||
22 | G2S | Associate 13 | Name 15 | 18 | ||||||||||||||||||||||||||
23 | G2S | Associate 14 | Name 16 | 19 | ||||||||||||||||||||||||||
24 | G2S | Associate 15 | Name 17 | 20 | ||||||||||||||||||||||||||
25 | G2S | Associate 16 | Name 18 | 21 | ||||||||||||||||||||||||||
26 | G2S | Associate 17 | Name 19 | 22 | ||||||||||||||||||||||||||
27 | ||||||||||||||||||||||||||||||
28 | ||||||||||||||||||||||||||||||
Holidays |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | B2 | =Setup!$D$11&" Holiday Tracker" |
R2:AC2 | R2 | =IF(R4="SH","",IF(R4="BH","", IF(WEEKDAY(R6)=1,"", IF(WEEKDAY(R6)=7,"",COUNTIF(R8:R28,Setup!$D$27)+COUNTIF(R8:R28,Setup!$D$28)+COUNTIF(R8:R28,Setup!$D$29)+COUNTIF(R8:R28,Setup!$D$30)+COUNTIF(R8:R28,Setup!$D$31)+COUNTIF(R8:R28,Setup!$D$32)+COUNTIF(R8:R28,Setup!$D$33)+COUNTIF(R8:R28,Setup!$D$34)+COUNTIF(R8:R28,Setup!$D$35)+COUNTIF(R8:R28,Setup!$D$36)+COUNTIF(R8:R28,Setup!$D$37))))) |
P3:AA3 | P3 | =IF(P4="", IF(P7>=$I$3, IF(WEEKDAY(P6)=1, IF(Q3="SH",O5,""), IF(WEEKDAY(P6)=2,R5, IF(WEEKDAY(P6)=3,Q5, IF(WEEKDAY(P6)=4,P5, IF(WEEKDAY(P6)=5,O5, IF(WEEKDAY(P6)=6,N5, IF(WEEKDAY(P6)=7,""))))))),P3),P4) |
AB3 | AB3 | =IF(AB4="", IF(AB7>=$I$3, IF(WEEKDAY(AB6)=1, IF(AC3="SH",AA5,""), IF(WEEKDAY(AB6)=2,#REF!, IF(WEEKDAY(AB6)=3,AC5, IF(WEEKDAY(AB6)=4,AB5, IF(WEEKDAY(AB6)=5,AA5, IF(WEEKDAY(AB6)=6,Z5, IF(WEEKDAY(AB6)=7,""))))))),AB3),AB4) |
AC3 | AC3 | =IF(AC4="", IF(AC7>=$I$3, IF(WEEKDAY(AC6)=1, IF(#REF!="SH",AB5,""), IF(WEEKDAY(AC6)=2,#REF!, IF(WEEKDAY(AC6)=3,#REF!, IF(WEEKDAY(AC6)=4,AC5, IF(WEEKDAY(AC6)=5,AB5, IF(WEEKDAY(AC6)=6,AA5, IF(WEEKDAY(AC6)=7,""))))))),AC3),AC4) |
B4 | B4 | ="Total Days Remaining: "&SUM(N:N) |
P6:Q6 | P6 | =WEEKDAY(P7) |
R6:AC6 | R6 | =R7 |
P7:AC7 | P7 | =O7+1 |
N8:N28 | N8 | =IFERROR(IF(D8="","",SUM(K8+M8+L8-COUNTIF(O8:AD8,Setup!$D$27)-COUNTIF(O8:AD8,Setup!$D$28)-COUNTIF(O8:AD8,Setup!$D$29)-COUNTIF(O8:AD8,Setup!$D$30)-COUNTIF(O8:AD8,Setup!$D$31)-COUNTIF(O8:AD8,Setup!$D$32)-COUNTIF(O8:AD8,Setup!$D$33)-COUNTIF(O8:AD8,Setup!$D$34)-COUNTIF(O8:AD8,Setup!$D$35)-COUNTIF(O8:AD8,Setup!$D$36)-COUNTIF(O8:AD8,Setup!$D$37))),(IF(D8="","",SUM(K8+M8-COUNTIF(O8:AD8,Setup!$D$27)-COUNTIF(O8:AD8,Setup!$D$28)-COUNTIF(O8:AD8,Setup!$D$29)-COUNTIF(O8:AD8,Setup!$D$30)-COUNTIF(O8:AD8,Setup!$D$31)-COUNTIF(O8:AD8,Setup!$D$32)-COUNTIF(O8:AD8,Setup!$D$33)-COUNTIF(O8:AD8,Setup!$D$34)-COUNTIF(O8:AD8,Setup!$D$35)-COUNTIF(O8:AD8,Setup!$D$36)-COUNTIF(O8:AD8,Setup!$D$37))))) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B8:AD28 | Expression | =$N8<0 | text | NO |
O8:AD28 | Cell Value | contains "DENIED" | text | NO |
O8:AD28 | Cell Value | contains "ALLOC" | text | NO |
O8:AD28 | Cell | does not contain a blank value | text | NO |
O6:AD28 | Expression | =O$3="BH" | text | NO |
O5:AD7 | Expression | =O$3="Nights" | text | NO |
O5:AD7 | Expression | =O$3="Backs" | text | NO |
O5:AD7 | Expression | =O$3="Days" | text | NO |
D:E | Cell Value | duplicates | text | NO |
O5:AD5 | Expression | =O$3="BH" | text | NO |
O6:AD28 | Expression | =O$3="SD" | text | NO |
O5:AD5 | Expression | =O$3="SD" | text | NO |
O5:AD5 | Expression | =O$3="SH" | text | NO |
O6:AD28 | Expression | =O$3="SH" | text | NO |
O6:AD28 | Expression | =IF(WEEKDAY(O$7)=7,TRUE) | text | NO |
O6:AD28 | Expression | =IF(WEEKDAY(O$7)=1,TRUE) | text | NO |
O5:AD5 | Expression | =IF(WEEKDAY(O$7)=7,TRUE) | text | YES |
O5:AD5 | Expression | =IF(WEEKDAY(O$7)=1,TRUE) | text | YES |
O5:AD28,O2:P2,R2:AD2 | Expression | =(O$2+0>=Setup!$D$17) | text | YES |
O8:AD28 | Expression | =$N8=0 | text | NO |
N2,N50:N1048576,N8:N28 | Expression | =$N2=0 | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
O4:AC4 | List | =Setup!$F$30:$F$33 |
O8:AC28 | List | =Setup!$D$27:$D$38 |