Ronin Global
  • Home
  • Articles
    • How-To: Smartsheet Help
    • Enhancement Requests: Smartsheet
    • How To: Zapier Help
    • Business Practices
    • Posts by J. Craig Williams
  • Smartsheet
  • Zapier
  • References
    • Color Codes
  • About Rōnin Global
    • About Craig
  • Home
  • / How-To: Smartsheet Help
  • /
  • Smartsheet: Transposing Project Schedule

Smartsheet: Transposing Project Schedule

2017-11-29How-To: Smartsheet HelpSmartsheet

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:

Project Schedule

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.

https://xkcd.com/1667/

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.

 

Comments

Erin Mallory 2017-11-29 at 11:03 - Reply

Hi Craig, thank you for sharing your process for this. This might be a silly question, but do the cells on the horizontal sheet link to the cells on the intermediary sheet, or directly back to the project sheet?

Thanks!
Erin

    J. Craig Williams 2017-11-29 at 11:08 - Reply

    Not in this version.
    The flow is from the Project Sheet to the Transpose/Intermediate Sheet to the Horizontal Sheet.
    For some Project/Program/Portfolio type arrangements, the connections may flow in both directions (but not to/from the same cells, sadly).
    Does that answer the question?

    Craig

Erin Mallory 2017-11-29 at 15:47 - Reply

Yes, thanks!

Write a Reply or Comment Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Archives

  • May 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018
  • July 2018
  • February 2018
  • November 2017
  • June 2017
  • May 2017
  • April 2017
  • March 2017
  • February 2017
  • January 2017
  • December 2016
  • November 2016
  • July 2016
  • May 2016
  • April 2016

Calendar

November 2017
M T W T F S S
« Jun   Feb »
 12345
6789101112
13141516171819
20212223242526
27282930  

Categories

  • Articles
  • Business Practices
  • Enhancement Requests: Smartsheet
  • How To: Zapier Help
  • How-To: Smartsheet Help
  • Politics
  • Posts by J. Craig Williams

@2017-2021 Ronin Global | Theme by speakinginvector