Introduction (too long, don't read)
I have a data-arrangement problem. I'm now up to three qualifying files from coworkers, and for that matter, I've run into a couple of questions here on SE recently on which I cannot begin to offer help. The Sheets in question take a form where what I think of as "Records" will be arranged in a grid rather than rows. What I consider one or two sets of Related data is then aligned with the Records in the two available dimensions: columns and rows. It's such a natural approach that I cannot call it a "spreadsheet anti-pattern", but it does make the application of native functions arduous.
For example, I post one video per week to each of six channels. I want to plan for the rest of the year. Even just mentioning "52 weeks times 6 channels" prompts thoughts of a table arrangement. I know—I'll list weeks down the rows, the six channels across the columns, and fill my "schedule matrix" with video subjects. It is instantly understood by others. I can just add weeks as time passes. As I change plans, shifting already-scheduled videos around, swapping channels, etc. are trivial GUI operations. Oh, but when I scratch the surface, I'll see that I've broken everything else beautiful about spreadsheets.
The Goal
How can I take values arranged in a grid and have them reflected in a list, live, elsewhere?
In my sandbox Sheets file (view/comment or make a copy), I have coworker Reps arranged in the Teams and Focus roles they chose. It is sparsely populated, with 23 reps not shown as they have yet to sign up.
I have then manually rearranged everything in various ways off to the side. I wish to make one (or more) of those side lists automatically. At present, the only thing automatic is some Conditional Formatting. Here we see a list of the slots still vacant.
My research has yielded little but bespoke or narrowly-applicable approaches. Or absurdly complex formulas and queries, so convoluted as to be unmaintainable. Plus this all gets much worse when they inevitably add another dimension of relationship; say… the videos should all have a title in another cell, and a link in another… or the PR Reps all need contact info listed. If only it was all done in proper tables from the start!
Without throwing in the Sheets-towel and migrating to a database or dumping their precious layout, are there clean, native spreadsheet ways to do this? To make ongoing use of a Sheet arranged as a record-per-cell Grid and dismantle it to record-per-row Table?
