Employees are selected in their corresponding shift on each month tab in a dynamic calendar See 092022 tab in my example:

See 092022 tab in my example

I want Pemanence tab will be able to retrieve automatically the values in the good current month tab based on TODAY's and TOMORROW's dates as I filled manually the values here : enter image description here

I share with you my work as I need your help https://docs.google.com/spreadsheets/d/1zMgnIVAy71M_LX7VMs2IqnBgxO4eVZhatNkfjh0OSEw/edit?usp=sharing

Thanks in adavance

1 Answer 1


With a similar approach than the other question, if you keep the exact same format than in sheet 092022 you can use this formula:

=INDIRECT(TEXT(Month(C7),"00")&year(C7)&"!R"&6+13*ROUNDUP((Day(C7)-weekday(C7,2))/7,0)&"C"&2*WEEKDAY(C7,2)-1&":R"&17+13*ROUNDUP((DAY(C7)-weekday(C7,2))/7,0)&"C"&2*WEEKDAY(C7,2), FALSE)

See it working in this spreadsheet: https://docs.google.com/spreadsheets/d/1zMgnIVAy71M_LX7VMs2IqnBgxO4eVZhatNkfjh0OSEw/edit?usp=drivesdk

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.