Auto Assign Staff Names to a list of dates in Google Sheets

[jl2509]

Hi Everyone,

I have shared a google sheet Roster Sheet
with the issue I have where I need some help.

The list of names tab needs to populate the Roster columns to the end of the months for the year 2023 and stop at the end of the month to continue at the next month without breaking the order of he names listed.

Is this possible please?

Your support is much appreciated.

 

[Fluff]

How about in A2 dragged down

=INDEX(Names!$A$1:$A$8,MOD(B2-DATE(2023,1,1),8)+1)

and change B2 to D2 for the next column.

 

[jl2509]

Genius Fluff,

Much appreciated.

Any chance you could explain how that formula work for my understanding please.

 

[Fluff]

The Mod function will return numbers from 0 to 7 to which we add 1 & that tells the index function what row to return.

 

[jl2509]

Is there a way to make this dynamic based on the names being reduced or increased rather than manually changing the formula?

 

[Fluff]

How about

=INDEX(FILTER(Names!$A$1:$A$100,Names!$A$1:$A$100<>""),MOD(B2-DATE(2023,1,1),ROWS(FILTER(Names!$A$1:$A$100,Names!$A$1:$A$100<>"")))+1)

 

[jl2509]

Great stuff Fluff
One last question.

Is there a way to do this using importrange in Cell A2, C2, E2 etc

 

[Fluff]

Absolutely no idea. I Don't use Sheets.

 

[jl2509]

Ok no worries, your formula works great so I will use this for now and put it out to see if any can fathom it using importrange.

 

[Fluff]

Ok