Introduction (too long, do not read)
I have a problem of data arrangement. I am now processing up to three colleague qualification files. In fact, I recently met some questions about SE that I can not begin to offer help. The sheets in question take a form in which what I consider "recordings" will be stored in a grid rather than in rows. What I consider one or two sets of associated data is then aligned with the records in the two available dimensions: columns and rows. It's such a natural approach that I can not call it an "anti-template spreadsheet", but it makes the application of native functions arduous.
For example, I post a video a week on each of the six channels. I want to plan the rest of the year. The mere mention of "52 weeks 6 times" suggests a table arrangement. I know – I'll list the weeks, the six channels in the columns, and fill out my "planning matrix" with video topics. He is instantly understood by others. I can just add weeks over time. When I change plans, move already scheduled videos, exchange channels, etc. are trivial operations for the GUI. Oh, but when I scratch the surface, I'll find that I've broken everything that's nice about spreadsheets.
How can I take values in a grid and have them reflected in a list, live elsewhere?
In my sandbox data sheets file (view / comment or make a copy), I have representatives of colleagues arranged in the Teams and Focus roles that they have chosen. It is sparsely populated, with 23 unrepresented representatives as they still have to register.
I then rearranged everything manually in different ways. I want to do one (or more) of these automatic side lists. At the moment, the only thing automatic is conditional formatting. Here we see a list of slots that are still vacant.
My research has resulted in few, but tailor-made or closely applicable approaches. Or absurdly complex formulas and queries, so complicated that they are intolerable. Moreover, all this becomes much worse when they inevitably add another dimension of the relationship; say … the videos must all have a title in another cell and a link in another … or PR representatives all need the listed contact information. If only everything was done in the appropriate tables from the beginning!
Without throwing away the Sheets napkin and without migrating to a database or emptying their valuable layout, are there native and clean spreadsheet methods to do this? To permanently use a sheet arranged in the form of a recording grid per cell and dismantle it into a recording table per line?