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

Matthew Barrett · 11/22/21

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?

BullandKhmer · 11/22/21

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å · 11/24/21

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!

Matthew Barrett · 11/24/21

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å · 11/24/21

@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)

Matthew Barrett · 11/24/21

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 · 01/29/22

@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.

Matthew Barrett · 02/04/22

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

asish lamichhane · 03/27/23

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.

exported each sheets as excel
set up an data shuttle workflow to add excel files to data table and trigger the workflow upon attachment

Camilo Meza · 08/31/23

https://community.smartsheet.com/discussion/comment/311737#Comment_311737

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

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.
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.

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

Answers

Help Article Resources

Categories