Sign in to submit new ideas and vote
Get Started

Copy Specific Columns From One Sheet to Another, Linked

135

Comments

  • Erik Hartman
    Erik Hartman ✭✭✭
    edited 04/11/23

    Would be great, as an automation, to copy cell data from one column to another column.


    Use case:

    The workflow triggers an automation based on a change in a cell dropdown value. Once this triggers occurs the workflow processes, and upon completion, there is the ability to copy cell level data in one cell to another cell. Would be great if this is not limited to the same sheet but have the ability to copy cell level data across multiple sheets.

  • NikkiOno
    NikkiOno ✭✭✭✭

    I would love to be able to only move certain row information to a desired sheet based on certain criteria rather than moving the entire row.

  • Although automations give the ability to copy rows to another sheet, it lacks the ability to update rows (in the 2nd sheet) when data within the original sheet changes. It would be helpful to have a bi-directional connection that when the master row changes information the copied row changes as well in the connected sheet.

  • @pgregory3 Definitely something I'd recommend looking into DataMesh for!

    Danielle W.

    Product Marketing

    Smartsheet

  • Hi @Andrée Starå

    Sorry just reading through this discussion. Can you help to explain once I create the so called helper sheet then what would is next steps?

  • Emily T.
    Emily T. ✭✭✭

    It would be helpful for me if in the automations there was a Copy Rows and Link, that would link to the original project plan so that it would update automatically. The issue I'm having is that I'm creating a milestone dashboard with gantt view, but need to be able to change the colors of the gantt chart, which can only be done in the grid sheet. Smartsheets does not allow for changing the gantt colors of lines that are considered headers, subheaders, etc. Only task lines. So now I'm copying rows to another sheet, linking them to the main project plan so they'll automatically update, changing the gantt colors, so I can then create a report showing the actual information I need in order to put in my dashboard. There's got to be an easier way.

  • My situation is this. We are maxing out at the 20K cell limit and have to create sheet after sheet to archive this data. We are trying to create a new sheet that would copy only 2-3 columns from those sheets using automation so we can track asset counts and not go over the 20K cell limit. If we could select which columns to copy over during the automation, this would save us so much time as we now have to do sections at a time, allow them to copy and then delete the columns that are not needed. This will sometimes cause the automation to stop working until we correct the columns. It is a huge pain point.

  • vdemattei
    vdemattei ✭✭✭✭

    This would also be helpful for our uses. A row is created when an applicant fills out an application form. That applicant is then the primary column for all subsequent sheets. We use the sheets to track the workflows through different phases of the project: intake, contract, construction, close out. We move a row to the next phase sheet using automations. Every time a row is moved, it moves all of the information collected from the initial application form AND all of the previous workflow tracking columns along with it. By the time we get to the last phase, I have to hide many more columns than I'm showing. I really only want to move a few items from phase to phase (applicant name and maybe a few other things). Then, we have other sheets that track other items like payment. I copy a row to the payment tracker and EVERYTHING else is copied with it. All I need is the name and the estimate amount, really. If our processes were any more complicated, we would absolutely run into the column limit and all of this excess information is bound to bog down the process. It already takes multiple seconds (which feels like a long time when everything else works instantly) for automations to trigger.

  • Tim Starkey
    Tim Starkey ✭✭✭✭✭

    Would love to see this implemented. Just ran into it today. Our specific use case is our IT ticketing system.

    We have a single form that helps the user drill down to the specific request/need. Some of these requests include starting up a new process. (For example, opening a new client portal account to our file server). Our main IT ticket sheet is large as it handles many different types of requests. Some requests only live in the ticket sheet until resolved. Some (like our file server access) get copied over to that sheet and is handled there.

    But now, my file server sheet (listing active/inactive accounts, expiration dates, owner, etc) which only needs to be about 5 columns has about 30 extra columns from the IT ticket system that I have to 'hide'. Not an elegant solution. And whenever I want to add a new workflow in that sheet, I have to wade through all the extra columns to get to the 5 I actually need.

    So yes, this would be a huge benefit.

    As for implementation, it could be a simple as a checkbox in the automation that asks "create new columns in destination sheet if they do not exist?", or as granular as "select columns to copy".

    FWIW, our IT intake sheet would not need to have so many columns, but its kind of a function of how smartsheet forms work. Because the help text prompt may vary based on what has been selected, or because I may not want to show ALL of the drop down options based on prior selections, I end up with columns that are variations of the same thing (helper columns) that all get copied over into the main description field. That is a request for another day.


    Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @OORF @Erik Hartman @NikkiOno @pgregory3 @Emily T. @Prime George @vdemattei @Tim Starkey @VLD

    I hope you're well and safe!

    It would be fantastic to be able to select specific columns, but in the meanwhile, did you see my workaround? (details below)

    Here's a possible workaround or workarounds

    You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Katrina_27

    I missed your answer. Did you get it working?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi there, thanks for sharing. I have used this approach in a sheet but I'm not aware of a way to include a formula as part of a workflow automation.


    Please share if you have any suggestions on how to leverage a formula as part of a workflow.


    Thanks

  • Hi @Andrée Starå. We have been using helper sheets from the start of this project. We are continuing this method. It is not ideal but it works. It just adds a bit of time on our end. Thank you for checking in!

  • Darren Mullen
    Darren Mullen ✭✭✭✭✭✭

    @OORF @Erik Hartman @NikkiOno @pgregory3 @Emily T. @Prime George @vdemattei @Tim Starkey @VLD

    In case you are looking for another solution, I am now setup to offer an add-on solution for the purpose of copying specific columns selectively from one sheet to another!



  • vdemattei
    vdemattei ✭✭✭✭

    I know this idea/request has been on the "wishlist" for at least 9 months now. Are there any updates?

    I appreciate all the work arounds presented, but I am really hoping Smartsheet incorporates this functionality into their standard product.