How can I populate a roster with a pre-existing template of allocations?

Topic Labels: Base design
594 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi folks,

I’m responsible for creating and maintaining a roster that includes five people. I’ve been using excel to automate as much of it as possible in order to reduce human error and the amount of time I need to spend doing it. But this approach still requires sending a printed or pdf document and team members manually transcribing what it says to their own calendars.

Ultimately, I’d like to produce a calendar that the whole team has access to, so it can subscribe to it using their digital calendar service of choice:


I’ve put the people in a table named ‘Crew’:


The five people follow a pattern of allocations on weekdays that repeats every four weeks. For example, James Holden usually covers Monday of week A (A1), and Amos Burton usually covers Wednesday of week C (C3):


On my ‘roster’ table I started with the date column, calculated the weekday to return ‘ddd’, the week to return ‘W’, and also concatenated week and weekday into ‘A1’ format:

roster grid

The ‘Covering’ column contains the formula IF({Templated cover}=BLANK(),'Unallocated',{Templated cover})

I’d like to fill the column ‘Templated cover’ with the person from the ‘Template’ table who covers that week/weekday combination. For example, on 9/11/21 then ‘Templated cover’ would return ‘Alex Kamal’ since he covers B2 recurrently. On excel I used an index/match array formula so that when I update the template, the allocations or ‘Templated cover’ update automatically. Can I do the same thing with airtable? I’m struggling to find a solution.

Thanks in advance

0 Replies 0