Copying a Hierarchy to an other sheet


If my users attempt to copy a hierarchy from one sheet to another, they loose the hierarchy information.

Using copy rows is not an option - because it messes up the formulas that are built into other cells in the sheet.

Copying the sheet is also not an option. We use Control Center, when we start a new project, CC spins up a package of about 20 different project sheets, with multiple cross links and roll up reporting to PMO sub teams.

I have users that have complex project plans, and sometimes a section of them are repeated from project to project, and they would like to copy them over, but when they do, they have to spend time messing around to get the hierarchy back.


  • David Dolch
    David Dolch ✭✭✭✭✭

    How exactly does copy and paste work in Smartsheet - specifically between different browser tabs?

    The article / learning section is not helpful

    Some things will copy between sheets, and other things (hierarchy, contacts, etc) will not

    Also, right clicking and selecting "paste" gives different results than "CTRL-V". I don't understand that at all, maybe it is a system issue on my end, but I get wildly different results (neither of which is what I am looking for) when I use the different methods.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    This won't answer your entire question, however I can help a little with row copy and formulas.

    Say I need to get data copied from Sheet A to Sheet C. Sheet A has one set of formula columns and then some manual entry columns. Sheet C needs the manual entry data and some of the calculated data from Sheet A, but also has its own formula columns.

    How I've handled this is by using a Sheet B as an intermediary. In Sheet B, I use formulas or cell links to grab only the data I need from Sheet A. Sheet B will not contain any columns that have the same name as any formula columns in Sheet C. When I use row copy functionality to send rows from Sheet B to Sheet C, the formula columns in Sheet C will not be overwritten, and can then perform calculations on the other data columns copied from Sheet B.

    Regarding Copy/Paste vs Ctrl-C/Ctrl-V: Smartsheet does not like Copy/Paste. Smartsheet only performs consistently with Ctrl-C/Ctrl-V. I don't know why that it is, but that's how it is. 🤷‍♂️


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • David Dolch
    David Dolch ✭✭✭✭✭

    Thanks for the reply, but unfortunately that is not going to work for me either. It is a good solution for people who know what they are doing, but for my average user there is too much of a risk of them breaking stuff.

    I have a project management, reporting, and business intelligence ecosystem built into SS in a Control Center environment. I have 35+ project managers running 100+ active projects at any one time. Control Center spins up a "package" of 20+ sheets every time a user creates a new project in the ecosystem. Control Center creates links that roll up reporting at a PM level, an intermediate team level, and my entire PMO level. All of the various formula columns (hidden and visible) are used to manage this reporting and to automate critical issue escalations, alert Sr Managers to new projects or status changes, etc.

    The last thing I need is having users (some of which are not licensed) creating additional sheets in order to copy and paste stakeholder lists or chunks of a project plan, and in the process messing up formulas which I then have to track down and fix.

    It seems that a lot of the solutions for Smartsheet involved building helper sheets and doing things that involve a decent knowledge of Smartsheet itself. Not a problem for me or the other person that administers out solution. But my users just want to copy and paste, not use some work around.

    It really is kind of disappointing.

    I do appreciate you sharing the workaround you developed. I may use it for some of my personal stuff, but I doubt I will deploy it in my CC solution.