Index & Match from two worksheets.

[J2022]

Hello everyone,
As everyone can see I am new to this forum and am a novice excel user. I usually search online and learn by applying certain formulas and functions.
Hence I am here seeking help from the experts to resolve my issue.

My spreadsheet has 14 tabs viz. Daily Dashboard, Weekly Report & 12 months of the year. The 12 months sheet is where daily data entry is done with time values of when reports are sent. Daily Dashboard shows the status of reports being sent on current day. Date format that used is dd/mm/yyyy

Issue I am facing is with the weekly report worksheet when it has to extract the time values for a week that starts in one month and ends in the next month.
Example: If work week starts on 04/04/2022 and ends on 09/04/2022, I get the values for the whole week but if the work week start on 28/04/2022 and ends on 02/04/2022, I will get values only until 31/03/2022 and N/A for 1st & 2nd. Obviously, I understand that it won't give me a result for April as the formula is doing a Match to month of March only.

I did find some solutions where the match can be done to 2 or more sheets but I was unable to execute it in my application. So I would appreciate if someone can help me with this or provide an alternate solution if Index/match function is not the correct approach.

I have also posted this in Mrexcel forum as well but have not got an answer, so I am almost in the belief that Index/Match is not the right way to go for this solution.
I will post in my next post as forum rules does not allow in the first post.

https://www.mrexcel.com/board/thread...-week.1201826/

The excel spreadsheet I am attaching has no sensitive data as this is still work in progress. It's has got some random figures that i input for testing.

I hope the above information is clear.

Thanks
Best Regards
J

[AliGW]

You can provide the URL to the thread on Mr Excel by simply adding two spaces to it. Please do this. Thanks.

[J2022]

Hi Ali,

I had gone through several of your solutions on related subject. Thank you for your valuable contributions.
However, when I couldn't find the right solution for my application, or maybe I was not smart enough to understand to apply it, I decided to post my query.
I removed the https and dots from the link. Hope you are able to access it.

//wwwmrexcelcom/board/threads/index-match-values-for-a-working-week.1201826/

[AliGW]

I've added the URL to your opening post.

[Glenn Kennedy]

Since the sheets all have exactly the same layout, you can get rid of that ghastly formula and use:

=INDEX(INDIRECT("'"&TEXT(D$7,"mmmm")&"'!C5:AG39"),ROWS(D$8:D8),DAY(D$7))

[J2022]

Since the sheets all have exactly the same layout, you can get rid of that ghastly formula and use:

=INDEX(INDIRECT("'"&TEXT(D$7,"mmmm")&"'!C5:AG39"),ROWS(D$8:D8),DAY(D$7))

ghastly ... It sure is when compared to the simple one you shared and sure does looks like it has solved my roadblock on that tab.

I will test it again tomorrow morning with more data in future months and confirm.

Thanks Glenn for a quick solution.

Best Regards
J

[Glenn Kennedy]

Grand. Let me know what happens...

[J2022]

Grand. Let me know what happens...

Hi Glenn, it works perfect.
I think there might be some impact at the end of the year. I am guessing I will be able to fix the lookup accordingly. Fingers Crossed.
But this was exactly what I wanted and your solution was just brilliant.

Thank you
Regards
J