Combining multiple sheets

Hi,

I have 7 sheets and I'd like to combine them automatically into a single sheet. They all have the same column structure. I have created an automation workflow to Copy the data into a new blank sheet, but the automation workflow only copies a single record that is triggered. Is there a way to copy an entire sheet into another sheet via a workflow?

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

    When do you want it to run? You can schedule it to run at a specific time, and then use a criteria that grabs everything. Like create a column and leave it blank, and use IS BLANK as the criteria.

  • I want to run it manually, so maybe a dropdown or just a minor edit to a record, anything to trigger the automation to run. I created a criteria by using an actual field (all rows are filled with data) and I used IS NOT BLANK, but it only copied over the record that had a small change. Should it be copying all of the data?

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

    Hi @Fredster

    I hope you're well and safe!

    Here's a possible workaround or workarounds

    • Add a checkbox in the sheet or the sheet summary and then reference it on all rows, so when you check the box, it will check all boxes on the rows, and then you'd use that to trigger the Workflow.
    • Using Cross-sheet formulas to a so-called Master Sheet that collects all the seven sheets together. Either use a VLOOKUP or INDEX/MATCH structure to connect the sheets and when you update the source sheet, it will reflect on the destination sheet.

    Make sense?

    Would any of those options 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 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Fredster
    Fredster
    edited 04/28/22

    Thanks for the ideas. I experimented a bit, and accidentally deleted my data! I was able to create a report that combined the data. Vlookup is a bit beyond my skill set right now. The tough part about the report I realize is that I can't do any automation on it.

  • FWPBIDev
    FWPBIDev ✭✭✭

    Smartsheet: making the simple complex since 2005!

    Here's what I've learned the past few months. If you need transformations quickly, particularly on just medium to average sized datasets or larger, you're better off staying in Excel, applying your transformations there, and then uploading results to Smartsheet for simple automation and reporting.

    Tried to endorse SS 100% as our PMO but the amount of time it takes to do the simple, just isn't worth it, though it is a decent reporting tool...

    Or, you can pay SS an arm and a leg for connectors that should be included in base packages.

    Those seem to be your best options. Good luck!