Cross Sheets in action with multiple formulas

SkiPatrolScott

Much thanks to @Paul Newcome @Genevieve P. @Kelly Moore @Ipshita and others for continued help with formulas

We are using Smartsheet with our Ski Patrol to track Patroller-Route integration. It is a full circle data grab. Many of these inputs are still being gathered.

Not in this order of creation, The Avalanche Route Stats (ARS) {sheet} has our list of routes, and the route qualification list (some routes overlap). We can sort sheet order, # of routes run, most shots used, rank (based on routes run) or.... This sheet is being feed by an Avalanche Route Assignment (ARA) {sheet} and a Route Qual Builder (RQB) {Sheet}. Columns left of [Route Qual] are real time data from our (ARA), Columns right of [ROUTE QUAL] are from real time data from (RQB). Managers can now verify the routes we run most often, and see how many patrollers are LEAD Qualed, Second Qualed and are currently identified as a Priority Trainee.

The ARA, is filled out for reduction days, it can be made for available on a Dashboard for real time viewing. Should other departments need or want to make decisions based on reduction progress they can view the Dashboard and not have to guess or call and interrupt the Reduction IC. The ARS is counting routes and shots from this sheet. We often are running over 20 routes.

The RQB, lists all the routes a patroller is qualed to lead or second. The ARS is counting the Routes to let managers know how many patrollers are Qualed for each route. We are still determining what % of staff is minimal based on route frequency. The data for this sheet is currently being gathered.

All of these sheets come together in the BLEND (yes I know it needs a better name). This sheet identifies the Patroller, Routes, Quals. The BLEND, takes data from the ARA, it counts the #of Lead or Second for each route. In the same cell, RQB is referenced to show if that Patroller is a LEAD, SECOND or Priority Trainee. This sheet will be most effective once all RQB data has been gathered.

Practical uses. Managers can view the ARS, determine which routes are lacking Lead Qual patrollers. They can then view the BLEND, and choose patrollers who are cusping Lead Qual status and assign them Priority Trainee in the RQB. If a route/patroller is shown in yellow they should be considered for that route assignment to increase the Lead Qual pool. Managers can view the BLEND, and see while Patroller X is Lead Qual they have not run that route, so better to assign them that route then give it to Patroller R for a 5th time.

The RQS can be utilized to determine the routes that should be learned first, so that patrollers progress, learning the most used, instead of just throwing spaghetti against the wall to see what sticks.

Moving forward, I hope to create a Taskbook, where patrollers will track their own history. After each route, they will get an EVAL from the Lead. Then the patroller can petition managers to be Qualed.

I also need to create a SHEET with all the formulas and how and where they are being used.. as there are many other sheets that we are using that are disseminating other data points.

IanN

This is such an awesome use case. I agree that creating a sheet to track all the formulas and where/how they're being used is EXTREMELY helpful for a)our Support team b)your support folks c)anyone else needing to understand the solution. An alternative is to use the Column Description or Sheet Summary (unless you're already using this) to detail that information; that way it's housed on the sheet the formulas are on.

I have a friend who does Ski Patrol and has been mentioning their outdated methods. I'll have to pass this along to them!