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:
- Keep checking the date of the corresponding column
- Keep check of the song name (should be unique enough but can be combined with an artist for super flexibility)
- 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.
- Search this name in the availability sheet and check whether that person is there on the date from step 1.
- Repeat step 3 and 4 for all parts.
- 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