Display a work status table during summer vacation in a spreadsheet! Streamline phone handling for duty officers and school log entries

Creating a work status summary spreadsheet for summer vacation and function explanation

This article explains how to create a spreadsheet to streamline phone handling for duty officers and school log entries by displaying work status during summer vacation in a summary table. It assumes there are 50 individual schedule sheets, each containing morning and afternoon plans, as well as the name and destination of tasks such as training, business trips, or official leave.

Summary tables to be created

1. Work summary table by date: Displays dates from July 21 to August 31 on the vertical axis and individual morning and afternoon plans on the horizontal axis.

2. Work summary table by individual: Displays individual names on the vertical axis and dates from July 21 to August 31, along with morning and afternoon plans, on the horizontal axis.

3. Staff movement summary table for duty officers: Select a date to display the staff movements for that day.

Spreadsheet structure

• Individual schedule sheets: 50 individual sheets. Sheet names are managed with sequential numbers like "1", "2", ..., "50". It is assumed that for each sheet, morning plans are entered in column C and afternoon plans in column D within the range C6:D47 for each date.

• Work summary table by date sheet: A sheet that displays individual plans by date

• Work summary table by individual sheet: A sheet that displays plans by date for each individual

• Staff movement summary table for duty officers: A sheet that displays staff movements for the selected date

Creation of each summary table and function explanation

1. Work summary table by date

This summary table displays individual morning and afternoon plans by date horizontally from 50 sheets.

Formula: Just by entering this one function in cell C4, the summary table is completed

=REDUCE(TOROW(,1),SEQUENCE(1, 50),
LAMBDA(pv,cv, HSTACK(pv,INDIRECT(cv & "!C6:D47"))))

Purpose of the function:

The purpose of this function is to horizontally concatenate and display data from a specific range (C6:D47) existing in multiple Excel sheets (sheet names "1", "2", "3"... "50") into a single sheet. In other words, the data from C6:D47 of each sheet is arranged in consecutive columns on one sheet.

Components and explanation of the function:

1. `TOROW(,1)`:

   • The `TOROW` function is a function that converts an array or range into a single row.

   • `,1` is an argument used to create an empty row. It is used as the initial value for the `REDUCE` function, and data from each sheet is added here.

2. `SEQUENCE(1, 50)`:

   • The `SEQUENCE` function generates a sequence of numbers of a specified length starting from a specified value.

   • `1, 50` means to generate a sequence from 1 to 50. This sequence is used as sheet names.

3. `LAMBDA(pv, cv, HSTACK(pv, INDIRECT(cv & "!C6:D47")))`:

   • The `LAMBDA` function is used to define an anonymous function.

   • `pv`: The accumulated result (the result of the previous LAMBDA function). The initial value is an empty row created by `TOROW(,1)`.

   • `cv`: The current value in the sequence (1 to 50).

   • `HSTACK(pv, INDIRECT(cv & "!C6:D47"))`:

     • `INDIRECT(cv & "!C6:D47")`: The `INDIRECT` function interprets a string as a cell reference. In this part, it uses the current sequence value `cv` as the sheet name and references the range C6:D47. For example, if `cv` is 1, it becomes `INDIRECT("1!C6:D47")`, referencing the range C6:D47 on sheet "1".

     • `HSTACK(pv, ...)`: The `HSTACK` function concatenates arrays or ranges horizontally. In this part, it joins the previous accumulated result `pv` with the C6:D47 data extracted from the current sheet horizontally.

Processing flow:

1. `SEQUENCE(1, 50)` generates a sequence from 1 to 50.

2. The `REDUCE` function starts, and an empty row created by `TOROW(,1)` is set as the initial value.

3. The `REDUCE` function repeatedly applies the `LAMBDA` function to each value in the sequence (1 to 50).

4. In the `LAMBDA` function, the current sequence number is used as the sheet name, and the `INDIRECT` function is used to reference the range C6:D47.

5. The `HSTACK` function is used to horizontally combine the previous cumulative result with the data extracted from the current sheet's C6:D47 range.

6. Once the `REDUCE` function finishes applying the `LAMBDA` function to all sequence numbers, it returns the final cumulative result.

7. As the final cumulative result, the data from C6:D47 of all sheets is displayed, concatenated horizontally.

Prerequisites:

• Sheet names must be numbered sequentially as "1", "2", "3"... "50".

• Data must exist in the C6:D47 range on each sheet.

This function is a convenient tool for efficiently collecting data from multiple sheets and displaying it all in one sheet. While it requires the prerequisite that sheet names be numbered sequentially, it is a very powerful function under those conditions.

2. Individual Work Schedule List

This list is useful for understanding when and what kind of schedule a specific individual has.

Formula: Just by entering this one function into cell C4, the list is completed.

=MAP(A4:A53,
LAMBDA(_v,
TOROW(indirect(_v&"!C6:D42"))
)
)

Purpose of the function:

The purpose of this function is to extract data from a specific range (C6:D42) from each individual's sheet based on the specified individual name number (A4:A53) and display it as a list by concatenating it horizontally.

Components and explanation of the function:

1. `MAP(array1, lambda_function)`

   • The `MAP` function applies the specified lambda function to each element of the specified array (`array1`) and returns the result as a new array.

   • `array1`: In this case, it is the cell range specified by `A4:A53`. It is assumed that the individual name numbers (sheet names) are entered as a list in this range.

   • `lambda_function`: In this case, it is the lambda function defined by `LAMBDA(_v, ...)`.

2. `LAMBDA(_v, TOROW(INDIRECT(_v&"!C6:D42")))`

   • The `LAMBDA` function defines an anonymous function (a function without a name).

   • `v`: The current array element passed by the `MAP` function (in this case, the personal name number (sheet name) entered in cells A4:A53). Variables starting with an underscore `_` are conventionally used to indicate "unused variables" or "unimportant variables," but in this case, it is actually an important variable because it is used within the `INDIRECT` function.

   • `TOROW(INDIRECT(_v&"!C6:D42"))`: This part performs the actual processing of extracting data and concatenating it horizontally.

3. `INDIRECT(_v&"!C6:D42")`

   • The `INDIRECT` function references a cell range specified by a string.

   • `_v&"!C6:D42"`: Concatenates `_v` (personal name or sheet name) and the cell range "!C6:D42" to create a string for the referenced cell range. For example, if `_v` is "1", it becomes `INDIRECT("1!C6:D42")`, referencing the cell range C6:D42 on the sheet named "1".

   • This function returns the data in the C6:D42 range of the specified individual (sheet) as an array.

4. `TOROW(...)`

   • The `TOROW` function converts a specified array or range into a single-row array.

   • It takes the array returned by the `INDIRECT` function (data in the C6:D42 range) and concatenates it horizontally into a single-row array. This creates a list where each individual's data is arranged horizontally.

Processing flow:

1. The `MAP` function applies a lambda function to each personal name number (sheet name) in the cell range `A4:A53`.

2. The lambda function uses the given personal name number `_v` to perform the following processing:

   • Uses the `INDIRECT` function to reference the cell range C6:D42 of the sheet named `_v`.

   • Uses the `TOROW` function to concatenate the data in the referenced cell range horizontally into a single-row array.

3. The `MAP` function returns an array where the results of the lambda function (the horizontally concatenated data for each individual) are arranged vertically.

Prerequisites:

• The personal name numbers (sheet names) must be entered as a list in the cell range `A4:A53`.

• A sheet corresponding to each personal name number (sheet name) must exist, and data must exist in the range "C6:D42" on that sheet.

• The data to be displayed in the list must be appropriately entered in the range C6:D42.

• It is desirable for the data in each row to be arranged in chronological order (to improve the readability of the list).

With this function, based on the specified personal name number, the data extracted from each individual's sheet is concatenated horizontally to create a list for each person.

3. Staff Activity List

When you select a date on the sheet for the person on duty, the staff activity corresponding to that date is displayed in a list.

Purpose of the function:
This function is intended to display a list of staff activities corresponding to a date when that date is selected from a calendar by double-clicking on the sheet for the person on duty. It assumes that the staff activities are recorded with dates and activities on a separate sheet (in this case, the sheet named "1").

Formula: Just enter this function once in cell D5 to complete the list.

= LET(r,
xmatch($C$2,'1'!A:A),
MAP(B5:B54,
LAMBDA(v,
indirect(v&JOIN(r,"!C",":G",""))
)
)
)

Components and explanation of the function:

1. `LET(name1, value1, name2, value2, ..., expression)`

   • The `LET` function makes formulas easier to read and manage by assigning names to values used within the formula.

   • `name1, value1`: Defines the variable name and its value.

   • `expression`: The formula that performs calculations using the defined variables.

2. `r, XMATCH($C$2,'1'!A:A)`

   • `r`: Variable name. This variable stores the row number where the date matches.

   • `XMATCH($C$2,'1'!A:A)`: The `XMATCH` function returns the position where the specified value is first found in an array.

     • `$C$2`: Search value. This cell is assumed to contain the date selected from the calendar. The `$` symbol is used to make the cell reference absolute.

     • `'1'!A:A`: Search range. Specifies the entire column A of the sheet named "1" as the search range. This column is assumed to contain a list of dates.

     • The `XMATCH` function returns the row number where the date in `$C$2` is first found within the range `'1'!A:A`. This row number is stored in the variable `r`.

3. `MAP(B5:B54, LAMBDA(v, INDIRECT(v&JOIN(r,"!C",":G",""))))`

   • The `MAP` function applies the specified lambda function (`LAMBDA(v, ...)`) to each element of the specified array (`B5:B54`) and returns the results as a new array.

   • `B5:B54`: Array. This range is expected to contain a list of individual name numbers (sheet names).

   • `LAMBDA(v, INDIRECT(v&JOIN(r,"!C",":G","")))`: Lambda function.

4. `LAMBDA(v, INDIRECT(v&JOIN(r,"!C",":G","")))`

   • The `LAMBDA` function defines an anonymous function (a function without a name).

   • `v`: The current array element passed by the `MAP` function (in this case, the individual name number (sheet name) entered in the cells of `B5:B54`).

   • `INDIRECT(v&JOIN(r,"!C",":G","")))`: This part performs the actual processing to extract staff movements.

5. `JOIN(r,"!C",":G","")`

   • The `JOIN` function concatenates multiple strings into a single string.

   • `r`: The row number obtained by the `XMATCH` function (the row number where the date matches).

   • `"!C"`: A string. Concatenates the sheet name, "!", and "C".

   • `":G"`: A string. Concatenates ":" and "G".

   • `""`: A delimiter. Since no delimiter is specified here, an empty string "" is used.

   • The `JOIN` function concatenates these strings to create the string for the cell range to be referenced. For example, if `r` is 10, `JOIN(r,"!C",":G","")` returns the string "!C10:G".

6. `INDIRECT(v&JOIN(r,"!C",":G",""))`

   • The `INDIRECT` function references a cell range specified by a string.

   • `v&JOIN(r,"!C",":G","")`: Concatenates the personal name number (sheet name) `v` with the cell range string created by the `JOIN` function. For example, if `v` is "1" and `JOIN(r,"!C",":G","")` is "!C10:G", it becomes `INDIRECT("1!C10:G")`, referencing the range from column C to column G in row 10 of sheet "1".

   • The `INDIRECT` function returns the value of the specified cell range.

Process flow:

1. The `XMATCH` function searches for the row number where the date entered in `$C$2` is first found in column A of sheet "1" and stores it in the variable `r`.

2. The `MAP` function applies a lambda function to the personal name numbers (sheet names) in the cell range `B5:B54`.

3. The lambda function uses the given personal name number (sheet name) `v` to perform the following processing:

   • Uses the `JOIN` function to concatenate `r` (the row number where the date matches) with the cell range string to create the string for the cell range to be referenced.

   • Uses the `INDIRECT` function to extract staff status based on the created cell range string.

4. The `MAP` function returns an array with the results of the lambda function (each staff member's status) arranged vertically.

Prerequisites:

• Dates must be entered as a list in column A of the sheet named "1".

• The date selected from the calendar must be entered in cell `$C$2`.

• Personal name numbers (sheet names) must be entered as a list in the cell range `B5:B54`.

• A sheet for each staff member must exist, and the sheet name must match the personal name number (sheet name).

• Staff status must be recorded in columns C through G of the row matching the date in each staff member's sheet.

With this function, when you select a date on the duty sheet, the staff status corresponding to that date is displayed in a list.

4. Links to shared data:

Summer Vacation Staff Work Status Spreadsheet

Clicking 'Make a copy' will save it to your My Drive.

Disclaimer

• No permission is required to download or use the data I have posted.

• You are welcome to share it within your school, but please refrain from redistributing it on the internet, such as on social media or websites (including redistribution after modification).

Summary: Creating a summer vacation work status list and streamlining duty tasks

To ensure smooth school operations during summer vacation, it is important to grasp the work status of faculty and staff and streamline duty tasks. This article explained how to create the following two work status lists to support duty tasks.

1. Work list by date: You can see at a glance who has what schedule on a specific date.

2. Individual Work Schedule: Allows you to track the schedule and availability of specific staff members.

3. Duty Officer Staff Status List: Select a date to view the status of staff members for that day.

These tables can be created in Google Sheets by combining functions such as `LET`, `MAP`, `SEQUENCE`, `INDIRECT`, `XMATCH`, and `JOIN`. In particular, the `INDIRECT` function is a very powerful tool for aggregating data from multiple sheets because it allows you to dynamically specify sheet names and cell ranges as strings.

For duty officer tasks, you can significantly streamline phone responses, visitor reception, and school log entries by setting it up so that simply selecting a date from a calendar displays a list of staff status for that day.

However, since the `INDIRECT` function tends to be computationally intensive, you should be mindful of performance if you have a large number of sheets or a high volume of data. Consider reviewing and using more efficient functions or formula structures as needed.

By using the information explained in this article to customize your spreadsheet to fit your school's specific situation, you can expect to make summer vacation duty tasks run more smoothly and help reduce the burden on staff.