I am using google sheets to have a group of people fill out when they are available and what it to show up on another spreadsheet and be able to move as well. 0 signifies they are free and 1 means they are unavailable.
EXAMPLE TABLE BELOW. I need John and all of the times below him until the next name to show up on a different document so that John only needs to input his availability once and I can call his availability based on the name in a departments roster. so say he moved departments going from the Finance roster list to the Marketing roster list. His availability would travel with him without him having to input it into the new roster manually.
So far I have it written like so.
=QUERY('Availability'!A2:H, "select K, L, M, N, O, P, Q, R where K matches '"& Roster!F6 &"'")
I am not sure how to make it select the next 8 rows below the row that their name on the roster. All this phrase pulls up is the name and the days of the month, I have looking into row_number() and other similar items but wouldn't know how to implement it into this query.
I am really new to this and do not have any coding/database SQL background at all. Any help is much appreciated or at least confirmation that this isn't possible would be much appreciated.
| Name | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|---|---|---|---|---|---|---|---|
| John | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
| 3-4 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4-5 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5-6 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6-7 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7-8 PM | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
| 8-9 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10-11 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 11-12 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Greg | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
| 3-4 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4-5 PM | 1 | 0 | 0 | 0 | 0 | 1 | 0 |
| 5-6 PM | 1 | 0 | 0 | 0 | 0 | 1 | 0 |
| 6-7 PM | 1 | 0 | 0 | 0 | 0 | 1 | 0 |
| 7-8 PM | 0 | 0 | 1 | 0 | 0 | 1 | 0 |
| 8-9 PM | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 10-11 PM | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| 11-12 PM | 0 | 0 | 0 | 0 | 0 | 1 | 0 |
| Sam | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
| 3-4 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4-5 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 5-6 PM | 1 | 0 | 1 | 1 | 1 | 0 | 0 |
| 6-7 PM | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 7-8 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 8-9 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10-11 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 11-12 PM | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Best Answer
@Andrew, it is always going to be best for you to share a link to a sample spreadsheet (e.g., one that contains the same sheet names as in your real spreadsheet, with similar data setup on each sheet). Otherwise, you are asking volunteers here to spend our own time setting up a sheet to match your details, copying over the data ourselves, testing it, then coming back here and explaining it without being able to share the visual reference unless link to our own Drive, etc. Without doing all of that, we can only "eye it" here and try to write a formula without testing.
That said, I will give you my best guess after eyeing your formula above. Try this:
If this doesn't work, I can only encourage you to share that link with the link's Share permission set to "Anyone with the link can edit."
I won't explain this formula, since we don't yet know if it works.