Tag: smartsheet

Smartsheet for Task and Project Management

I recently started to use Smartsheet for helping to manage my tasks, duties, and projects. I found an existing template from them and am using it pretty much as-is. Our college has been using Smartsheet for a couple of years now and it’s been very good overall.

My template features a pretty standard array of fields:

  • Task
  • Category
  • Status
  • WBS No.
  • Assigned to
  • Start Date
  • End Date
  • Duration
  • % Done
  • Comments
  • Predecessors
  • and the standard Smartsheets Conversations (comments), Attachments, etc.

This tool replaces what I had been doing previously. Not long after the start of COVID, I was overwhelmed with work. I don’t mind juggling, but I didn’t even know what I was juggling at that point. My previous system wasn’t working for me so I switched to using hidden, free blocks on an alternate, private Outlook calendar. This helped me to block off time for what I needed to do without cluttering up my main calendar which is shared.

Back to Smartsheet, I’m still able to get a good view of what I need to do and manage everything decently. I’m a visual person, so I’m mostly missing out on the graphical view of a calendar with blocks for time to work on projects. That’s been OK so far. I do have the Gantt feature for larger projects, but I still don’t use that view much. It’s mostly useful for the dates.

Replacement Plan in Smartsheet

Our leadership is looking at better documentation around Replacement Planning and while we have a lot of the data in existing Smartsheets, I need to work with others to better communicate that data.

Ultimately, we want to share who’s working on what project and who others can go to for “leadership” and “project” backups in the event that the principle is unavailable. In this time of COVID-19, we have determined that it may be necessary to document up to three backup tiers for each project.

Initially, the difference between a “leadership” and “project” backup needed to be defined. Someone identified to provide leadership backup would be expected to know how to get answers related to a specific project but they themselves may be unable to complete the work or task. The person identified as a project backup would be able to accomplish the work that would yield answers to those questions.

The next big hurdle was to determine how to combine information from at least three different Smartsheets and using the Dashboard feature, we will be able to combine that information. We’re starting off with one sheet as the master that will establish the basic style (column names and widths) for the data. A second sheet was edited by changing some of the existing column names and adding new ones. A third sheet is going to prove more difficult. We need to preserve the original column names and will not be able to add more columns to that existing sheet as we had done in the second sheet.

I have decided to create a secondary sheet that pulls in that information using index and match functions and then other formulae to remap that data into columns that we are using in the other two sheets.

Smartsheet: Index, Match, @Row

I am trying to build out a new process using Smartsheet and was just recommended by Laura Adams to consider using Index & Match instead of VLookup because it requires less resources, makes it easier to reproduce, and is more secure.

index, match vs. vlookup

How to build an Index-Match formula

Example

Let’s say you had a simple sheet that listed the faculty contact for any given course. In another sheet, you would like to pull that information in and you can as long as you have a cell with the same course information. This allows you to have a number of smaller source sheets that contain useful information that needs to be referenced in a number of other sheets.

You would set up your index-match function by:

  1. identifying (referencing) the column in the source sheet with the names of the faculty
  2. identifying the column in the destination sheet that contains the course information for each of the faculty
  3. identifying the column in the source sheet that contains the course information you are matching to

I’ve created some public sheets which anyone should be able to view to see this very simple example of index-match at work:

It is easy to get confused what gets referenced where in the formula.

Laura’s example above includes an IFERROR function that helps to turn “#NO MATCH” errors into either blanks or some other more useful, human-friendly message.

Some additional resources: