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 |