Smartsheet: Transposing Project Schedule

Occasionally, I run across a requirement to show the tasks or dates from a project schedule in a more horizontal view.

That is, take something like this:

and display (and use) it like this:

What is shown is a simple example, only four columns to move over or link to the second Sheet, but I’ve worked with projects where several dozen key dates were shown in the original horizontal view. Typically, I’m helping convert a legacy schedule / task management system managed in Excel and they often naturally progress to this structure. As Randall Munroe pointed out in his XKCD comic, these can be quite complex.

Because of the number of cells that might be involved, I wanted some means to speed up the linking process between the two sheets.

 Summary Section

Since Smartsheet allows selection of a range when creating links between two sheets, I set up a Summary section with the key dates repeated. I use the Predecessor column with the SS (start-to-start) predecessor type to get the dates from the remainder of the schedule. Smartsheet does not allow formulas in the date columns used in its Project Settings.

 Transpose / Intermediate Sheet

Smartsheet does not have a transpose copy/paste functionality, so the four cells of concern can not simply be copied/pasted (as links) to the the horizontal sheet. To accomplish, an intermediary sheet is created.

Each project is given a section (using Smartsheet’s hierarchy functionality) with a [Project Dates] column which will hold the dates from the Project Sheet and Date columns for each of the columns of concern. A cell reference formula is used to display the dates from the [Project Dates] column in the appropriate column.

To connect the Project Sheet and the horizontal sheet, a series of Copy / Paste actions for performed.

1. Copy the dates from the Project Sheet

2. Paste Special on the Transpose Sheet

using the Link to copied cells option

and because of the  previously entered cell references, the dates are automatically displayed in the columns

3. Another Copy / Paste-Special to the horizontal sheet and the dates are now shown there as we wanted.

Further Improvements and Closing Thoughts

With the basic infrastructure in place, the Project Sheet can be used as a master/template for future projects, the Transpose Sheet can be set up to speed the process of getting the Copy/Paste-Special performed more quickly. For small numbers of cells, the time savings to get separate cells linked to another sheet is not that large. Most of the savings is in the Summary section, not in the Transpose sheet. For larger numbers of cells, the Transpose intermediary cuts the time in half or more. I have noticed no loss of functionality from using the intermediary sheet nor in speed of response to changes in the Project Sheet’s dates.

If you like this post, please “Like” it.

If you are new to Smartsheet and want to check it out, click here. If you have questions about this post, add them below. If you have questions about something else in Smartsheet, post on the Community. I occasionally stop by to answer them and so do a lot of other talented people.