Weekly rota - 52 worksheets 1 workbook

[GaryHealey]

Good afternoon all

I have been trying to create a planner or rota for several weeks now I have come across a problem I cannot find an answer to.

I have a sheet for each week of the year with each of my staff and also some formulas for the number of days sick and leave for each person using a COUNTIF, I then have a master sheet with a SUM to add up the total for each year, My problem is that if someone leaves or someone joins then all of my formulas are moved and no longer accurate.

I have tried a VLOOKUP with COUNTIF but that doesn't seem to work across all 52 sheets.

Can you please help me with where I'm going wrong?

Many thanks

Gary

 

[awoohaw]

can you post a portion of the summary worksheet and a few of the component worksheets using the xl2bb add in?

What are the formulas you use?

Going forward, are you open to a different approach to managing your time?

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(a,CHOOSECOLS(VSTACK('[Rota NMEA.xlsx]Week 13:Week 20'!$B$3:$J$20),1,9),SUM(FILTER(INDEX(a,,2),INDEX(a,,1)=A2,0)))

 

[GaryHealey]

can you post a portion of the summary worksheet and a few of the component worksheets using the xl2bb add in?

What are the formulas you use?

Going forward, are you open to a different approach to managing your time?

Hi
I tried to add the xl2bb addon, however, as I'm running this from a work laptop it is all locked down. I'm certainly open to a different approach to time management within reason.
Thank you for taking the time to reply to me.

 

[GaryHealey]

Hi & welcome to MrExcel.
How about

=LET(a,CHOOSECOLS(VSTACK('[Rota NMEA.xlsx]Week 13:Week 20'!$B$3:$J$20),1,9),SUM(FILTER(INDEX(a,,2),INDEX(a,,1)=A2,0)))

Hi, Thank you for such a warm welcome with such a fast reply, That string has worked perfectly, I have never heard of that formula but it is amazing, How do I start to learn the different functions that you have mentioned above as I thought I was very good at excel but now feel like a bit of a newb.
Many thanks
Gary

 

[Fluff]

Both choosecols and vstack are fairly new functions and you can find info on all the functions here Excel functions (alphabetical) - Microsoft Support

 

[GaryHealey]

Both choosecols and vstack are fairly new functions and you can find info on all the functions here Excel functions (alphabetical) - Microsoft Support

Thank you very much, I really appreciate it.

 

[Fluff]

You're welcome & thanks for the feedback.