Index/Match values for a working week

J

jgopal

New Member
Joined
Dec 27, 2020
Messages
23
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
  2. MacOS
Hi again everyone,

My next requirement from the same data entry set is to make a weekly report on a new worksheet called Weekly Report. First couple of columns will be the same with Report code number and target time, the next 6 columns for the 6 days of the the week that ended e.g.: 04/04/2022 to 09/04/2022 (I think the start and end date will be static and will be manually input). Once the start and end dates are entered, 6 columns will updated from the respective month sheet using index and match.

Domenic was kind enough to help me earlier for capturing for a single day. As I told him, I understood the logic but am not sure on how to write the syntax to achieve the desired output. I am sharing his valuable answer to my query earlier which helped me on my Daily Dashboard worksheet. Maybe the same can be used with some additional parameters to achieve the result I am looking for.

=INDEX(INDIRECT("'"&$B$2 &"'!"&CELL("address",$B3)&":"&CELL("address",$AF3)),MATCH($B$1,INDIRECT("'"&$B$2&"'!$B$2:$AF$2"),))

Details I had given Domenic last month with which he gave me the above solution:
Worksheet Name: Daily Dashboard
B1=TODAY()
B2=TEXT((B1),"mmmm")

Worksheet name: March, April, May......
Range B3:AF3: Manual time Input cells from 1st to 31st March for Report 1.
Range B2:AF2: Locked Cells with dates from 01 to 31.

Hope the above requirement is clear. If not, please let me know and I will try to explain better.

Thanks
Best Regards
J




Report Tracker.xlsm
ABCDEFGHIJKLMNOPQR
3DAYFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturdaySundayMondayTuesdayWednesdayThursdayFridaySaturday
4CODETIME01020304050607080910111213141516
5D1-111:30WEEKEND13:00WEEKEND
6D1-211:3012:00
7D1-2111:3011:00
8D1-2211:3011:35
9D1-311:3012:01
10D1-415:00
11D1-515:00
12D1-611:30
13D2-19:30
14D2-29:30
15D2-312:00
16D3-115:00
17W1-1
18W1-211:30
19W1-311:30
20W1-411:30
21W1-511:30
22W1-611:30
23W2-1
24W2-211:30
25W2-311:30
26W2-411:30
27W2-511:30
28W3-1
29W3-214:00
30W3-314:00
31U1-1
32U1-2
33U1-317:00
34U1-417:00
35U1-57:12
36U1-6
37U1-7
38U1-8
39U1-9
April
Cell Formulas
RangeFormula
C3:R3C3=TEXT(C4,"dddd")
 

Attachments

  • Report tracker.png
    Report tracker.png
    157.1 KB · Views: 20

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Hi everyone,

I managed to apply the above code shared by Domenic and got the required result however I still have a problem. Sharing xl2bb extract, please let me know if there a way to solve this.

The weekly report is for the working week's data analysis. So if the date range is March 28, 2022 - April 2, 2022, the lookup month for Match/Index will be only March sheet and it will not search for the data in April worksheet. Is there a further tweak that can be done to the code that can search both months?

Thanks
J

Report Tracker2.xlsm
ABCDEFGHIJKLMN
1INSERT START DATEINSERT END DATEWEEK START INWEEK ENDS IN
2
3CODETYPE & NAME OF REPORTSTARGET TIMEMon 04 AprTue 05 AprWed 06 AprThu 07 AprFri 08 AprSat 09 AprMon 04 AprSat 09 AprAprilApril
4D1-1REPORT 111:3013:0011:0012:0011:3013:0011:30
5D1-2REPORT 211:3012:0011:4511:5911:5912:1511:30
Weekly Report
Cell Formulas
RangeFormula
D3D3=K3
E3:I3E3=D3+1
D4:D5D4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($D$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($D$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
E4:E5E4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($E$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($E$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
F4:F5F4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($F$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($F$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
G4:G5G4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($G$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($G$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
H4:H5H4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($H$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($H$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
I4:I5I4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($I$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($I$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
L3L3=K3+5
M3:N3M3=TEXT((K3),"mmmm")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D5:I11Cell Valuecontains "NOT SENT"textYES
D5:I11Cell Value<=C5textYES
D5:I11Cell Valuebetween C5+TIME(0,1,0) and C5+TIME(0,30,0)textYES
D5:I11Cell Value>C5+TIME(0,30,0)textYES
D4:I4Cell Valuecontains "NOT SENT"textYES
D4:I4Cell Value<=C4textYES
D4:I4Cell Valuebetween C4+TIME(0,1,0) and C4+TIME(0,30,0)textYES
D4:I4Cell Value>C4+TIME(0,30,0)textYES
 

Attachments

  • Screen Shot 2022-04-11 at 15.41.57.png
    Screen Shot 2022-04-11 at 15.41.57.png
    196.3 KB · Views: 11
Upvote 0
Hello friends,

Upon searching a lot, I found this post which seems to be something similar or maybe the same thing that I am looking for.
Can anyone please help me in writing the correct syntax for my application.

Thanks
J

Report Tracker1204.xlsm
ABCDEFGHIJKLMN
1INSERT START DATEINSERT END DATEWEEK START INWEEK ENDS IN
2
3CODETYPE & NAME OF REPORTSTARGET TIMEMon 28 MarTue 29 MarWed 30 MarThu 31 MarFri 01 AprSat 02 AprMon 28 MarSat 02 AprMarchApril
4D1-1REPORT 111:3014:00NOT SENT11:3011:30#N/A#N/A
Weekly Report
Cell Formulas
RangeFormula
D3D3=K3
E3:I3E3=D3+1
D4D4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($D$3,INDIRECT($M$3 & "!$B$4:$AG$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($D$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
E4E4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($E$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($E$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
F4F4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($F$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($F$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
G4G4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($G$3,INDIRECT($M$3 & "!$B$4:$AG$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($G$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
H4H4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($H$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($H$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
I4I4=IF(INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AG5)), MATCH($I$3,INDIRECT($M$3 & "!$B$4:$AF$4"),))=0,"NOT SENT",INDEX(INDIRECT($M$3 & "!"&CELL("address",$B5)&":"&CELL("address",$AF5)), MATCH($I$3,INDIRECT($M$3 & "!$B$4:$AG$4"),)))
L3L3=K3+5
M3:N3M3=TEXT((K3),"mmmm")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G4Cell Valuecontains "NOT SENT"textYES
G4Cell Value<=F4textYES
G4Cell Valuebetween F4+TIME(0,1,0) and F4+TIME(0,30,0)textYES
G4Cell Value>F4+TIME(0,30,0)textYES
D4:F4,H4:I4Cell Valuecontains "NOT SENT"textYES
D4:F4,H4:I4Cell Value<=C4textYES
D4:F4,H4:I4Cell Valuebetween C4+TIME(0,1,0) and C4+TIME(0,30,0)textYES
D4:F4,H4:I4Cell Value>C4+TIME(0,30,0)textYES
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Index & Match from two worksheets.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
Fluff said:
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Index & Match from two worksheets.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
Click to expand...
Hi Fluff,

My sincere apologies for not reading all of the forum rules, in process duplicating the same query couple of days back and also for crossposting the same query today. I should have read all the Forum Rules which I didn't earlier. I have read the rules now and will ensure that I strictly abide by the rules henceforth.

To confirm I have not cross posted the query anywhere else.

Also, I believe that someone in the other forum has shared a solution. If it does fixes my issue above, should I share it here with a link to that post for anyone else and close it or just leave it open as it is? Kindly let me know how I should go about it.

Thanks
Regards
J
 
Upvote 0
If the solution does work, then please post the solution here & say it was on the cross post. That way you can mark the post as the solution.
 
Upvote 0
Solution was provided from another forum.
CROSS POST

I was told to use the below formula as the sheets all have exactly the same layout.
=INDEX(INDIRECT("'"&TEXT(D$7,"mmmm")&"'!C5:AG39"),ROWS(D$8:D8),DAY(D$7))

Hope this helps any one else looking for a solution like what I was looking for.

Regards
J
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

J
  • Locked
  • Solved
Index/Match with variable or dynamic sheet
Replies
8
Views
6K
Fluff
Fluff
F
  • Solved
Help finding a value with two conditions, one of them being a date and the other being text
2
Replies
11
Views
1K
frankieung
F
S
  • Question Question
Create Script Button to Add Selected Values to the Next empty Row in the sheet
Replies
1
Views
555
shaunreeder
S
R
  • Question Question
INDEX, MATCH, CONCATENATE Problem
Replies
1
Views
1K
DRSteele
DRSteele
M
  • Question Question
if functions nested with index and match functions
Replies
0
Views
525
mopo247
M

Forum statistics

Threads
1,224,818
Messages
6,181,152
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