Display progress of combined requirements based on values in other cells & sheets

Question

I am compiling a reference sheet in Google Sheets for our Music group. We play covers and everyone in the group is able to contribute suggestions. These suggestions make up sheet 1. This sheet contains the title and artist of a particular song, along with the needed parts (instruments etc.) and their player. It looks a bit like this: Artist Song | Vocals Guitar Bass Drums Keys U2 One | Bob Jeremy Alex Nina The xx intro | Jim Jeremy Betsy Carl Donald ABBA S.O.S. | Nina + Bob Lisa ? Jillian Bob

The U2 song here has no Keys part, so that cell is empty. The ABBA song has no designated Bass player yet. This song also features 2 singers (more on that later)

There is a second sheet to keep a record of the availability of the players. The left column has all players listed, top row has the dates we play. I'm using data validation to make sure only "Yes" or "No" is an option (or empty).

Lastly, there is a third sheet, which is where I'd like the magic to happen. This sheet shows only the songs from sheet 1 that have a full setup arranged. In the above example, this would be the first 2 songs on the list. I'm using Filter for this, as explained here. Now what I'd like to display next to each entry is whether the song can be played on a particular moment.

I've thought up a possible way to do this, but since I'm still a beginner, I have no idea what syntax to use. My idea of how to tackle this:

1. Keep checking the date of the corresponding column
2. Keep check of the song name (should be unique enough but can be combined with an artist for super flexibility)
3. Cross-reference the song with the first sheet and look for the first part needed (either empty or has a name). In case of empty go to the next part.
4. Search this name in the availability sheet and check whether that person is there on the date from step 1.
5. Repeat step 3 and 4 for all parts.
6. Present the result.

If possible I would like to keep a tally of how many parts are already confirmed (for instance 4/5 parts are confirmed available), if this stays within the realm of possibilities :) Also, how would I tackle multiple parts of the same (could make more columns..) or multiple players on the same part (would be very nice to have this but I have a feeling this can't be done really..)? In the above example for the ABBA song, if Bob is available but Nina is not.

Is my thought process (somewhat) correct? If yes, how would one accomplish such a feat?

EDIT: Here is a link to a full-fledged sheet, just like the one we use

Answer 1

well, the magic consists of 4 sub-magic steps and one bonus magic:

1: this will list names of players (musicians) from a given cover (song), sorted from A-Z and without empty cells all into one column

=TRANSPOSE(SORT(FILTER(Suggestions!F4:Q4,Suggestions!F4:Q4<>""),4,TRUE))

2: this will check and cross reference if at given date are musicians avail with musicians that are needed to play given cover, and then it will list only persons that are avail that day

=IF($B2<>"",(IFERROR(FILTER(
 (SORT(FILTER(Availability!$A$3:$A,Availability!B$3:B="YES"))),
 REGEXMATCH(
 (SORT(FILTER(Availability!$A$3:$A,Availability!B$3:B="YES"))),
 $B2)),"")),"")

3: then there is this minor formula that basically converts matching person's names to number 1, because the previous formula has a lots of errors that are just hidden

=IF($B2<>"",(IF($B2=C2,1,"")),"")

4: and finally this shall then make final calculations to check if given cover can be performed at given date with avail musicians

=IF((COUNTIF(Calculations!$B$2:$B$30,"<>")=(SUM(Calculations!N$2:N$30))),"YES","NO")

SIDE NOTE: you cant use this kind of format: Franca/Jerome. if you want to have 2+ people under one category in "Suggestions" sheet, then you must add 2+ columns and place each person in a different cell, because otherwise it will not calculate properly who can when play what - this step (fix) needs to be done as first thing!

and ofcourse you need to set up an additional sheet named Calculations and populate it with steps 1 - 3 and when done you can just hide it all. here is an example sheet (proof that it works and how it works): https://docs.google.com/spreadsheets/d/