Get Shift Done: Tips and Tricks
Smartsheet is an online application that allows users to collaborate and organize projects and task lists. It avoids the issues that arise from multiple versions of spreadsheets, confounded by long email threads in which no one knows which data is current. Instead, Smartsheet makes it easy to keep track of tasks through a simple (or not so simple) to-do list.
Yet despite Smartsheet’s capabilities, teams can load up their sheets with an abundance of information and tracking points. Most bosses don’t have time to dig through everything their teams created; they want a high level view of a project’s progress. The boss just needs the basics of the completed work.
Fortunately, Smartsheet made it possible to not only link cells from other sheets, but also made it easy to send the information to the boss in a way that she never even has to log in to see it.
Let’s say, for example, that our team has an upcoming workshop planned. Many tasks need to be completed to make the event run smoothly and to ensure that all promotions get done at the right time. I created a Smartsheet for the team to use, into which everyone could update task completion dates next to the due dates.
As part of tracking this workshop’s progress, the boss wants to see how well we do at meeting our due dates, and compare that with our performance on past workshops. Sure — no problem! We can make Susan happy by creating a Smartsheet just for her to access.
In order to share the summary data with Susan, though, we need to collect it. That means we need to create summaries in each individual workshop sheet.
In each Workshop sheet, I add a Total to the bottom. The first cell directly under the cell includes the formula
COUNT(Completed1:Completed45) showing us the total count of items in the Completed column. In the cell under the “On Time?” column we have
COUNTIF([On Time?]1:[On Time?]46, “NO”) totaling up the exact count of cells that include
No (as in: this task was not done on time).
Note: I’m rather fast-forwarding past a useful unique Smartsheet feature: its drop-down menus, such as the counts under Total. This allows us to move rows as we choose, in case we need to move them to the top or drag them down to add more tasks later, but that’s a topic for another time.
With the summary data being collected in the individual sheets, now it’s time to create the boss’s summary worksheet. To get started, choose
Create New and select
Create the column headings as you see below.
Tasks Total in the new sheet, link to the Total count that we just added for each workshop. Start with highlighting the cell to which you will link the data. Right click, and select
Link from Cell in another Sheet….
Choose the Sheet for the workshop we are linking from, and the exact cell that includes our total. For instance, in this image I link to the July 2016 checklist for a previous workshop.
Do this for each of the count totals we need to grab in all cells.
Next, calculate the percentage of the tasks that weren’t completed on time. Add
[Tasks Not Completed on Time]1 / [Tasks Total]1 to the cell next to our counts.
Since the boss wants to see just the totals and percentages, and doesn’t have time to log into Smartsheet, the status information — just those rows! — can be sent to her in email.
Highlight the two rows in your sheet, right click, and then select
Type in the boss’s email ID and include a message for her that explains what we are sending.
By default, Smartsheet would send the entire sheet. Instead, at least for the purpose of this example, she can be sent just the relevant columns. Click
OK and send.
The boss now gets an email with the team’s task counts and completion percentages, ideally showing how productivity has improved.
There are many ways in which you can link to other cells and sheets; this is just a simple example. With Smartsheet’s notification capabilities, the possibilities of use and engagement are endless.
GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.