look up with date ranges

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
333
Office Version
  1. 365
Platform
  1. Windows
i have this table in sheet 1 and sheet 2..I need a formula in sheet 1 (in yellow). that will auto populate based on the data in sheet 2 (in blue). wherein sheet 2 is the input table. I will input the "OFF" schedule that should appear in sheet 1... while the vacation (from start to end) should populate once the status is "OK"..Otherwise, "W" should appear.



4_Must-Have_AI_Tools (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1SHEET 1SHEET 2
2FEBRUARY 2023VACATION (VL)
301020304050607080910111213141516171819202122232425262728EMP.#OFFSTARTENDSTATUS
4EMP.#WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueNAME4329SatSun06-02-2308-02-23OK
5NAME1326WWWWWWWWWWWWWWWWWWWWWWWWWWWWNAME5330MonTue15-02-2317-02-23OK
6NAME2327WWWWWWWWWWWWWWWWWWWWWWWWWWWWNAME6331WedThu10-02-2312-02-23OK
7NAME3328WWWWWWWWWWWWWWWWWWWWWWWWWWWW
8NAME4329WWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWW
9NAME5330WWWWWOFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFF
10NAME6331WWWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWW
11NAME7332WWWWWWWWWWWWWWWWWWWWWWWWWWWW
12NAME8333WWWWWWWWWWWWWWWWWWWWWWWWWWWW
13NAME9334WWWWWWWWWWWWWWWWWWWWWWWWWWWW
14NAME10335WWWWWWWWWWWWWWWWWWWWWWWWWWWW
Sheet2
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Which is the meaning of the info in Sheet2 and what is there about EMP #326?
 
Upvote 0
Which is the meaning of the info in Sheet2 and what is there about EMP #326?
in sheet 2 you will find schedule "OFF" means the days they are off duty and the VACATION days with start to end..if the names/EMP.# did not show up in sheet 2 means they are "W" in sheet 1
1679759561126.png
 
Upvote 0
The columns AH and AI are shown as a date in the XL2BB minisheet; for example AH4= 44968 = Feb 11. Is it a date or a String?
So it is not mandatory that each Employee has an entry in Sheet2, right?
 
Upvote 0
Hi, I think i have a solution, but I think your sample has NAME6 first two days of February wrong. According to the rules they should be off:
(My solution is at the bottom, your original expecttions is unchanged and at the top (except for I deleted a lot of NAMES).

mr excel questions 17.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1SHEET 1
2Feb-23
3FebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFeb
4EMP.#01020304050607080910111213141516171819202122232425262728
5NAME4329WWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWW
6NAME5330WWWWWOFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFF
7NAME6331WWWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWW
8
9
10SHEET 2
11VACATION (VL)
12EMP.#OFFSTARTENDSTATUS
13NAME4329SatSun2023-02-062023-02-08OK
14NAME5330MonTue2023-02-152023-02-17OK
15NAME6331WedThu2023-02-102023-02-12OK
16
17Feb-23TRUE
18FebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFeb
19WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTue
20EMP.#01020304050607080910111213141516171819202122232425262728
21NAME4329WWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWW
22NAME5330WWWWWOFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFF
23NAME6331OFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWW
24
Excel Newbie2020
Cell Formulas
RangeFormula
I17I17=I20<=H13
C19:AD19C19=C20
C21:AD23C21=IF(SUM((--($A21=$C$13:$C$15))*(--(C$20>=$G$13:$G$15))*(--(C$20<=$H$13:$H$15)))>0,"V", IF(SUM((--($A21=$C$13:$C$15))*((--(WEEKDAY(C$20)=WEEKDAY($E$13:$E$15)))+(--(WEEKDAY(C$20)=WEEKDAY($F$13:$F$15)))))>0,"OFF","W"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21:AD23Expression=C21=C5textNO
 
Upvote 0
The columns AH and AI are shown as a date in the XL2BB minisheet; for example AH4= 44968 = Feb 11. Is it a date or a String?
So it is not mandatory that each Employee has an entry in Sheet2, right?
Maybe because i copy it from the dates but AH and AI are supposedly days..yes not mandatory
 
Upvote 0
My formula is a bit more complex than awoohaw's ....
It assumes that Off days are strings (those shown in col AH & AI), whereas data in Row 3 are "dates" and their format doesn't modify the calculated outputs.
It also assumes that if the same EMP# has more that 1 entry in the VACATION table, then the Off days are the same on all the entries (see the case for EMP# 3299, that I simulated have 2 vacation periods in February)
I understood that vacation periods that are not confermed by the Status=Ok are to be ignored

With reference to the XL2BB minisheets shown below, I calculated the outputs in the green table, so that its output can be compared with the original one
For the EMP# that are in Bold in column B, I modified their Vacation table for testing.
All that said, I used in Sheet1!C17 the formula
Code:
=LET(Tableee,Sheet2!$B$4:$H$200,EmplNum,$B5,myDate,C$4,myTb,FILTER(Tableee,INDEX(Tableee,0,2)=EmplNum),myTbl,IF(ISERROR(ROWS(myTb)),SEQUENCE(,5,99999,0),myTb),sorTbl,SORT(myTbl,5),myMatch,MATCH(myDate,INDEX(sorTbl,0,5)),mayBe,IF(UPPER(TEXT(myDate,"ddd"))=UPPER(INDEX(myTbl,1,3)),"Off",IF(UPPER(TEXT(myDate,"ddd"))=UPPER(INDEX(myTbl,1,4)),"Off",  IF(ISERROR(myMatch),"W",IF(myDate<=(INDEX(myTbl,myMatch,6)*(INDEX(myTbl,myMatch,7)="OK")),"V","W")))),mayBe)
Then copy it to the right and down
Beware: XL2BB formula is wrong (it shows a different formatting strings because my Excel doesn't use English)

To adapt it to you data layout you have to work on the following items:
Code:
Tableee,Sheet2!$B$4:$H$200             This is the position of Vacation table; leave enough rows for the table to grow
EmplNum,$B5                            This is the EMP# to work with
myDate,C$4                             This is the date
The absolute/relative addressing I used let copy the formula while keeping the right references


EDIT: I cannot insert the XL2BB minisheet for Sheet1, because it makes the message longer that 100000 charachters and that is forbidden; so please rfer to the Image


MULTI_C30325.xlsm
ABCDEFGH
1
2VACATION (VL)
3EMP.#OFFSTARTENDSTATUS
4NAME4329SabDom06/02/202308/02/2023ok
5NAME5330LunMar15/02/202317/02/2023OK
6NAME6331MerGio10/02/202312/02/2023OK
7NAME43299SabDom02/02/202308/02/2023OK
8NAME53309LunMar15/02/202317/02/2023OK
9NAME6339MerGio10/02/202312/02/2023OK
10NameXX327Gio
11NameXX32802/02/202304/02/2023
12NAME43299SabDom25/02/202303/03/2023OK
13
14
15
Sheet2
 
Last edited:
Upvote 1
:oops::oops:
...and the image doesn't show in the message above...
Let me re-try here

PS: I had to use an external image sharing service, strange...

 
Upvote 0
@ExcelNewbie2020 , I used a date value for all of my work in the suggestion I offered, the dates are just formated to display either month, weekday name, or day.
thanks man, but there is 1 condition missing..for the Vacation, the status should be "OK", sometimes there is empty cell or "NOT OK" under the status column..
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top