look up with date ranges

ExcelNewbie2020

ExcelNewbie2020

Active Member
Joined
Dec 3, 2020
Messages
350
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
 
Sort by date Sort by votes

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
you're right! It's too complex. It works on the sample data.. but it's giving me a hard time placing the formula on my actual data..
Click to expand...
Can you show at least an image of your real data? Did you check the "type" of the information? Which is the language for your Excel?

Point to check:
myDate must be a date (not a string); in the vacation table, the columns "Off" should be strings and need to correspond to the "short weeknames" in your language (example Sun for Sunnday); if your Excel language is not English then there is one formatting string in the formula to be modified; in the same Vacation table Start & End dates need to be Dates; the access key to the vacation table is EMP#

Again, with an image of your real data (make sure the cell coordinates be visible) maybe the things become clear
 
Upvote 0
Anthony47 said:
Can you show at least an image of your real data? Did you check the "type" of the information? Which is the language for your Excel?

Point to check:
myDate must be a date (not a string); in the vacation table, the columns "Off" should be strings and need to correspond to the "short weeknames" in your language (example Sun for Sunnday); if your Excel language is not English then there is one formatting string in the formula to be modified; in the same Vacation table Start & End dates need to be Dates; the access key to the vacation table is EMP#

Again, with an image of your real data (make sure the cell coordinates be visible) maybe the things become clear
Click to expand...
thanks man, the actual data is almost the same format except that there is an additional column after the emp#..
1679819174632.png
 
Upvote 0
ExcelNewbie2020 said:
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..
Click to expand...
Sorry about that miss. You didn't have any Not OK's in your lookup grid so I missed it. You also haven't addressed my question about Name3 first two days in the chart.

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-08
14NAME5330MonTue2023-02-152023-02-17OK
15NAME6331WedThu2023-02-102023-02-12OK
16
17Feb-23
18FebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFeb
19WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTue
20EMP.#01020304050607080910111213141516171819202122232425262728
21NAME4329WWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWW
22NAME5330WWWWWOFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFF
23NAME6331OFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWW
24
Excel Newbie2020
Cell Formulas
RangeFormula
C19:AD19C19=C20
C21:AD23C21=IF(SUM((--($A21=$C$13:$C$15))*(--($I$13:$I$15="OK"))*(--(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
Solution
Here is my correction that I posted in #14, but also includes one extra column.

mr excel questions 17.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
11VACATION (VL)
12EMP.#OFFSTARTENDSTATUS
13NAME4329SatSun2023-02-062023-02-08
14NAME5330MonTue2023-02-152023-02-17OK
15NAME6331WedThu2023-02-102023-02-12OK
16
17Feb-23
18FebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFeb
19WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTue
20EMP.#01020304050607080910111213141516171819202122232425262728
21NAME4329WWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWW
22NAME5330WWWWWOFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFF
23NAME6331OFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWW
24
Excel Newbie2020
Cell Formulas
RangeFormula
D19:AE19D19=D20
D21:AE23D21=IF(SUM((--($A21=$D$13:$D$15))*(--($J$13:$J$15="OK"))*(--(D$20>=$H$13:$H$15))*(--(D$20<=$I$13:$I$15)))>0,"V", IF(SUM((--($A21=$D$13:$D$15))*((--(WEEKDAY(D$20)=WEEKDAY($F$13:$F$15)))+(--(WEEKDAY(D$20)=WEEKDAY($G$13:$G$15)))))>0,"OFF","W"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D21:AE23Expression=D21=D5textNO
 
Upvote 0
thanks man, the actual data is almost the same format except that there is an additional column after the emp#..
Click to expand...
The additional column in Sheet1 is not a problem: you will insert the first formula in D5 (I guess); EmplNum shall point to $B5; myDate will point to D$4

The additional column in Vacation table makes a lot of difference, because the indexes of the information change.
Thus:
1) Tableee shall now point to an 8 columns wide range
2) There are several portions of the formula that look like INDEX(Tableee;0;X)
Any "X" that is higher the 2 need to be increased by 1; so 3 ->4, 4 ->5, 5 ->6, 6 ->7 and 7 ->8

Of course if you delete the extra column in the vacation table there should be no need for reworking the formula

Don't miss awoohaw's message, above
 
Upvote 0
awoohaw said:
Here is my correction that I posted in #14, but also includes one extra column.

mr excel questions 17.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
11VACATION (VL)
12EMP.#OFFSTARTENDSTATUS
13NAME4329SatSun2023-02-062023-02-08
14NAME5330MonTue2023-02-152023-02-17OK
15NAME6331WedThu2023-02-102023-02-12OK
16
17Feb-23
18FebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFebFeb
19WedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTueWedThuFriSatSunMonTue
20EMP.#01020304050607080910111213141516171819202122232425262728
21NAME4329WWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWWWWWOFFOFFWW
22NAME5330WWWWWOFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFF
23NAME6331OFFOFFWWWWWOFFOFFVVVWWOFFOFFWWWWWOFFOFFWWWWW
24
Excel Newbie2020
Cell Formulas
RangeFormula
D19:AE19D19=D20
D21:AE23D21=IF(SUM((--($A21=$D$13:$D$15))*(--($J$13:$J$15="OK"))*(--(D$20>=$H$13:$H$15))*(--(D$20<=$I$13:$I$15)))>0,"V", IF(SUM((--($A21=$D$13:$D$15))*((--(WEEKDAY(D$20)=WEEKDAY($F$13:$F$15)))+(--(WEEKDAY(D$20)=WEEKDAY($G$13:$G$15)))))>0,"OFF","W"))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D21:AE23Expression=D21=D5textNO
Click to expand...
this works sir thanks..
awoohaw said:
You also haven't addressed my question about Name3 first two days in the chart
Click to expand...
my apology, i didn't notice it.. your observation is correct.. I overlooked that days..
 
Upvote 0
Anthony47 said:
The additional column in Sheet1 is not a problem: you will insert the first formula in D5 (I guess); EmplNum shall point to $B5; myDate will point to D$4

The additional column in Vacation table makes a lot of difference, because the indexes of the information change.
Thus:
1) Tableee shall now point to an 8 columns wide range
2) There are several portions of the formula that look like INDEX(Tableee;0;X)
Any "X" that is higher the 2 need to be increased by 1; so 3 ->4, 4 ->5, 5 ->6, 6 ->7 and 7 ->8

Of course if you delete the extra column in the vacation table there should be no need for reworking the formula

Don't miss awoohaw's message, above
Click to expand...
thanks sir, your formula is too complex for me but I made it work thru your instruction.. All is good now..
 
Upvote 0
ExcelNewbie2020 said:
this works sir thanks..

my apology, i didn't notice it.. your observation is correct.. I overlooked that days..
Click to expand...

Thanks for the clarification. I have errors and omissions as well, as you know I did in this exercise ... it isn't necessarily a bad thing, and sometimes exploring why something is amiss reveals hidden errors or brings up new ideas to a solution.

i'm happy you have not 1 but 2 solutions for the question.

Have a great day!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Create a new row for every date in row and create multiple copies of data to new worksheet based on cell number value for each date in the row VBA
Replies
0
Views
29
Chris258
C
A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
271
Peter_SSs
Peter_SSs
B
  • Solved
Macro code changing date format
Replies
6
Views
735
Beneindias
B
A
  • Solved
Extraction last row based on specific words and put in account column(debit,credit)
Replies
4
Views
199
Alaa mg
A
A
  • Question Question
Populate Monthly Expenses Based on Date Range
Replies
3
Views
105
myall_blues
myall_blues

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

Share this page

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