0

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
1
  • Welcome. May I offer an observation... one reason that you are having problems with this analysis is that your database is not well designed. The data for each employee consists of a 7x8 cell matrix and this doesn't even identify the employee. While the existing layout has the advantage that the employee can see a whole week at a glance; it is not necessary (or perhaps even desirable) that this should be your database. This could be a data-entry screen and the data might be saved to your database. There is great value in having a more efficient, convenient and easier-to-use database.
    – Tedinoz
    Dec 9, 2020 at 11:24

1 Answer 1

0

@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:

=ArrayFormula(IFERROR(FILTER(Availability!A:H,ROW(Availability!A:A)>=VLOOKUP(Roster!F6,{Availability!A:A,ROW(Availability!A:A)},2,FALSE),ROW(Availability!A:A)<=VLOOKUP(Roster!F6,{Availability!A:A,ROW(Availability!A:A)},2,FALSE)+8)))

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.

1
  • This COULD be done with QUERY, but I think it would be more difficult to set up and, therefore, most likely more difficult for you to understand.
    – Erik Tyler
    Dec 8, 2020 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.