Index Match for Dynamically change in date rows based on each month

[prasath17]

Hi All,

I am working on a task where i have to populated the project timesheet for each resource based on the invoice data. First half of the task, i was able to achieve using Uipath. on the second half, since i felt it was complex to do Uipath i started adding formula in excel.

Please find the attached template excel where i have used Index match formula which is currently working for Nov.

But when i change From: value(First of the Month) in B5 , C12:A13 get re-arranged which is screwing up the formula.

How do i dynamically build the index match formula in this scenario? Please advice.

I am attaching my template sheet here.

[Mrrrr]

Why not simply leave the formula in C13 as:

Please Login or Register  to view this content.

If I reduced your formula to the one above, if I change the date from Nov 1st to any other 1st day of any other month, everything works fine.

In the formula from the sample file you have an extra (WEEKDAY($B$5;1)-(B10-1) before the IF function.

[prasath17]

Thank you so much Mrrrr. Just replaced the ; with , in the formula and it worked perfectly.

[Mrrrr]

Yes, it changed in my case 'cause of regional settings.

If this is solved, please go to this topic's top right - Thread Tools and click on "Mark this as solved" link.