Is there a way to combine multiple sheets into a single data table?

Options

I have 8 sheets that I need to combine into a single data table. The sheets are created external to Smartsheet and can be imported into Smartsheet using datashuttle. The columns are all the same in each sheet but the data changes frequently.

I can get all of the data together using a report but I need all of the data in a sheet so that I can add some additional columns with formulas.

Right now I am copying and pasting from the external data but these sheets update frequently and I want to automate updating.

Any advice?

Answers

  • BullandKhmer
    BullandKhmer ✭✭✭✭✭
    Options

    The easiest way I can think of is to set up your 8 sheets with correct columns but leave them blank.

    Then set up your 'master sheet'

    Then set up automation on each sheet - when a row is added - copy row to master sheet.

    Then import all your data into the blank sheets.


    There may be even easier ways, but this is my first impression.

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

    Hi @Matthew Barrett

    I hope you're well and safe!

    Do you have anything unique in the different sheets you import that we could use to "link" them together?

    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.

  • The reports are generated by group and the first column in each table is "Groupname" which is the only data unique to each sheet. I could add an index helper column and a unique# column to get a unique "Groupname Index#"

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

    @Matthew Barrett

    Yes, as long as each row could be tied together, it should work.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots of the sheets? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    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.

  • Here is a generalized description of the problem using non confidential information.

    After adding an index and unique identifier I have 8 sheets of data looking like this:

    Each block of data in the above picture is a separate sheet. Each sheet's index column actually goes to 4000 but the total number of unique rows sums to about 10000.

    The data is generated by "Peter", "Paul", and "Mary" who's business systems are completely separate. I need to combine those unique rows into a single sheet so that I can perform calculations on Product and Order data that feed into yet another business system.

    I can get all of the data into a report but I cannot reference the report data in another sheet to perform calculations.

  • AFlint
    AFlint ✭✭✭✭
    Options

    @Matthew Barrett Were you ever able to work out a solution to this problem? I have a very similar scenario and have not been able to get it to work, so I'm curious about your situation. Thank you for any updates.

  • I was not able to work out an effective solution in smartsheet.

  • asish lamichhane
    Options

    This is what i did, although tedious, i was able to complete this without an issue because i have 5 sheets with almost 20k rows in each.

    1. exported each sheets as excel
    2. set up an data shuttle workflow to add excel files to data table and trigger the workflow upon attachment
  • Camilo Meza
    Options

    A combination of what BullandKhmer suggests - automation - with DataMesh is what I would suggest.

    1. Sheet Automation on each of the 8 individual sheets that is triggered when a new line item is created and copies that data over to your "consolidated" sheet.
    2. DataMesh to help create links between your "consolidated" sheet and the individual sheets. This way, when cells within these individual sheets change then those changes are reflected in your "consolidated" sheet and update your calculations accordingly.

    An alternative would be to perform the calculations in such way they are pointing to the 8 individual sheets. For example a formula that sums data1 for project "A" in sheet #1 + data1 for project "A" in sheet #2 ...+ data1 for project "A" in sheet #8.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!