How can I populate a roster with a pre-existing template of allocations?
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:
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
