Google Sheets: data validation
Table of Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Scheduling grid setup . . . . . . . . . . . . . . . . . . . . . . . . 5 Concatenate data . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Data validation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Schedule formatting . . . . . . . . . . . . . . . . . . . . . . . . . 18 Schedule Legend . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Counting sessions . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Color coding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Schedule conflicts . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Digital Maestro Magazine
Page 3
Google Sheets: data validation
Introduction This is the third part in a five-part series that started on December 15, 2018. In that issue, we created a Google Form to collect information for conference participants, presenters, and vendors. That issue taught us how to use input validation. We used basic Google Form validation options. We also used Regular expressions to provide specific validation requirements. These included district email requirements and telephone formatting. The January 1, 2019 issue cleaned information collected with the form. Cleaning response information involved the use of several functions. We used the TRIM function to remove unwanted spaces. The SUBSTITUTE function was used to substitute characters for other information. We also used Regular Expressions to look for stray information we weren’t sure of and didn’t want to delete from the original records. All of this was done while maintaining the integrity of the original information.
This Issue In this issue, we will construct the scheduling platform. We will continue to use Google Sheets for scheduling. The sheet will include information for the rooms and times of each presentation. We will use Data Validation to select presenters for times and rooms. We will set up a Page 4
Digital Maestro Magazine
validation system to schedule multiple presentations for the same presenter. This is necessary so we don’t go over the agreed upon presentations. It is also necessary so we don’t miss or under-represent presenters.
Google Sheets: data validation
Scheduling grid setup We will be using the same spreadsheet used in the responses. We added sheets in the last issue to clean up the data. We will add another sheet for the schedule set up. This new sheet will import the clean data from the previous lesson.
Click the triangle to the right of the sheet name. A menu of options will open. Select the option to rename the sheet. We can also double click the sheet name.
A new sheet There is a button at the bottom of the spreadsheet that creates new sheets. This button is a plus symbol in the bottom left. It’s next to the first spreadsheet tab. Click this button to create a new sheet.
A new sheet is created and added to the right of the currently selected sheet. I was in the Schedule cleaning sheet. Click and drag the sheet tab to the right if it is not after the Schedule cleanup tab.
The sheet name will be highlighted. Rename the sheet to “Conference Schedule”.
Setup The presentation times for our conference will be placed on the left side. I will skip the first and second cells. These cells will be used for the rooms and a divider. In cell A3 I will place the time for the first event.
Digital Maestro Magazine
Page 5
Google Sheets: data validation The first event is our Keynote speaker. We don’t have any other events that take place concurrently with the Keynote. I like to place it here as an anchor for the rest of the schedule.
Each session at the conference is 50 minutes. The rest of the times are added with a ten-minute transition. The first session begins at 9:30. Sessions go all the way up until 3:30.
At the top of the sheet, I place the room names or room numbers. They are usually room numbers. The keynote is usually in the main gym of the high school. We like to maximize the number of participants per room. This is usually at around 20 to 25 per room. We take the total number of registered participant and divide by twenty. We average is
Page 6
Digital Maestro Magazine
about 500 participants. Dividing 20 into 500 gives us 25. This is the number of concurrent sessions we want. This is also the number of rooms we want. There are times when we have special spotlight presenters. They tend to draw larger participants to their sessions. We hold their sessions in larger rooms and increase the seating capacity accordingly. To compensate for their large draw we cut down the number of concurrent sessions. In this case, we would not need 25 concurrent sessions. The number of sessions is reduced to no fewer than 20. Spotlight presenters are placed in larger rooms. I place the rooms for the Keynote and spotlight presenters at the beginning. They are the easiest to set up because they are committed and will present all day.
To help visualize the schedule, I like to increase the size of the cells. This makes things easier to read. I also like to freeze the column with the session times and the row with the rooms. Freezing the column and row prevents the headings from disappearing when I need to scroll up or down.
Google Sheets: data validation Click once on cell A1. Click on View in the menu. Select Freeze and then the 1-row option.
A light grey bar will appear below the first row. This shows that the first row is frozen in place.
Click View in the menu again. Select Freeze and then select 1 column. The time and room information will always be visible as we scroll through the spreadsheet.
During the scheduling process, I need the presenter and session title information. I use this information to help distribute presentations throughout the day. To see the information clearly, I need to resize the cells. I’ll show you how I resize one row and column first before resizing all of them. Next to the letter for the column is an arrow. This arrow reveals a menu. Click the arrow for column B. A long menu with options for the column opens.
Go down the menu and look for the resize column option. Click the option when you find it.
Digital Maestro Magazine
Page 7
Google Sheets: data validation A resize configuration box will open. The default size for a column is 100. Replace 100 with 200 and click the OK button.
Right click on the row number. A menu with row options will appear. Look for the Row size option and select it.
The column is twice as wide as it was before.
Enter a value of 100 for the row height and click the OK button.
Resizing a row is done the same way. The menu option isn’t readily available for the row. We need to right click for the contextual menu. Click once on row 3.
This is the size all the remaining cells will be. Let’s resize the remaining cells in the schedule.
Page 8
Digital Maestro Magazine
Google Sheets: data validation Click once on row number 4.
All the rows up to row 9 will be resized.
We need to use the keyboard to help select the rows between row 4 and 9. Hold the shift key down and click on row number 9.
We will do the same with the column widths. Click once on column letter C.
Right-click on any row. Select resize rows 4-9 and enter a value of 100 in the configuration box.
Scroll right to the last column room number. Hold the Shift key and click on the column letter.
Digital Maestro Magazine
Page 9
Google Sheets: data validation Select the menu option on one of the column headers. Select the resize column option. The option should include all the columns we are going to resize. Change the value to 200 and click the OK button.
Scroll back to the first column and row. We are ready to configure the sessions for the schedule.
Page 10
Digital Maestro Magazine
Digital Maestro Magazine Thank you for reading these sample pages.
Purchase the full issue at‌
digitalmaestro.org/shop
Digital Maestro Magazine