I am trying to figure out how to use Google Sheets to allocate resources to tasks automatically based on multiple variables (like qualifications and preferences).
Lets say Task 1 takes place on week 31 only mornings and requires skills A and B. Task 2 takes place on week 32 only afternoons and requires skills B and C.
Then I have resource 1 who is available only mornings and selected weeks and has skills A, B and C. Resource 2 meanwhile is available whole day and has only skills B and C.
Now I'd need resource 1 to be automatically allocated to Task 1 and Resource 2 to Task 2. And of course I'd need to avoid situation where one resource gets allocated to two tasks in the same week.
Is this something that even is possible? I know I could use some advanced project management solutions but the team is already using Google Sheets and this is once a year scenario so acquiring entire separate solution is not so reasonable. If this was somehow not possible in Google Sheets but only in Excel, that would still be very useful.