Capstone Project Wiki

We have two different displays of the At-a-Glance schedules that are required: the Wiki view and an Excel view that can be customized by the team for their specific dates.

Because we don't want to worry about trying to keep the two different versions in sync, we have created an Excel workbook with macros that contains the raw data, then creates the output files for both displays from the same raw data.

AtaGlanceMaker.xlsx is the Excel workbook that is used to hold the raw data and to create the output files.

There should be a different copy of AtAGlanceMaker.xlsx for each school year. It typically has the school year appended to the file name (before the file extension), but that is not strictly required.

AtAGlance Maker.xlsx is currently stored in the Box folder under Capstone Master/Capstone Wiki Support Files/<School Year> Wiki Support. Here's a direct link to the Capstone Wiki Support Files folder.

There are 4 worksheets in AtAGlanceMaker that contain the raw data for creating the output files:

• Fall Student Data
• Fall Coach Data
• Winter Student Data
• Winter Coach Data

Each of these worksheets contains a row for each row of the At-A-Glance Schedule.

The first 8 rows of the worksheet are for header information. You can see the header information by looking at the sheets.

• Row 1 contains file name, font size, scaling information, and a description of the worksheet. This information seldom needs to be changed
• Row 2 contains version, worksheet title, workbook name, and sheetname information for the workbook to be created as an output file. NOTE: You will need to change the BoxID for the downloaded XLSX file for each of the schedules after you have created a new Class Documents folder each year. The BoxID is found in cell P2 of each of the schedule Data worksheets.
• Row 3 contains relative column width information. The total column factors should add to 1. ( Is this still true?)
• Row 4 contains information on the text alignment to be used in the Excel output file.
• Row 5 contains information on the grouping of the columns. If the cell in this row is not blank, it begins a group of N columns, where N is the number in the cell. That group will share a common heading.
• Row 6 contains the header for the given group in the output table
• Row 7 contains the header for the given column in the output table

If the number of header rows changes, or new information is added to the header rows, you will likely need to modify constants in the VBA module.

Rows 8 and higher contain the actual scheduled events for the At-A-Glance.

If you wish to have multiple entries in a given row, separate them with a carriage return (CMD-Shift-Enter on the Mac).

If you make any changes to the schedule entries (rows 8 and up), you must update the version number in cell C2.

The AtAGlance Master sheet contains text that will be copied into the output Excel files. It is unlikely that you will need to change this sheet.

The Test Data sheet is used for exploring possible changes to the Raw Data sheets. The macros do not make use of this sheet.

The Annual Schedule is used for the instructors to plan the annual schedule and assign responsibilities. The macros do not make use of this sheet, so it can be changes as desired without affecting any of the output files.

The Revision History sheet is used to track the revisions in AtAGlanceMaker (which are separate from the revisions in the raw data). Changes that affect all at-a-glance schedules generally are tracked in the Revision History sheet; changes that affect only a single schedule generally are tracked in the individual Raw Data sheet.

The Master sheet has some (very brief and only marginally helpful) instructions about the AtAGlance Maker. It also lists the version number that will be included in the output files, and has five buttons that can be used to create the output files.

Follow the following procedure to change the schedules.

1. Edit the Raw Data worksheets as desired. Be sure to bump the version number for each worksheet you modify
2. Save the workbook

After changing the schedules, you will need to create new output files.

1. Switch to the Master worksheet
2. Press the appropriate button(s) to create the output files corresponding to the changed data files. If you have changed the AtAGlance Maker version, press the Make All Docs button.
3. If you are on a Mac, you will need to give permission for Excel to create each of the output files. Just respond when the dialog prompt appears.

The newly-created output files must be installed for the students to have access to them

1. Copy the four newly-created excel files to Box: Class Documents <school year>/At-a-Glance Schedules.
2. Box should automatically replace the existing files and bump the version number
3. If not, what should you do?

1. Copy the four newly-created .txt files to the pages/ Wiki directory (see Wiki File Management for the location of this directory.
2. Commit the files to git, and push the changes to GitLab (see Using git for instructions).
3. Visit the Wiki Page At-A-Glance Schedules to make sure the new schedules are up.