How to combine & link multiple source sheets into one master sheet?

Jen Henning · 12/15/20

Hello - I have searched & searched and cannot find a solution to my challenge. Here's what I am trying to do.

1) I have multiple product developers working in their own "source" sheets, managing only their products with data points relevant to product development (item#, item description, vendor, cost)

2) Those products need to all come together into one sheet/report in which our merchants can manage additional data related to those products. (additional data - retail plan, dealer plan, retail price, in-store date, etc)

I can create a "report" that pulls all the source sheets together, however, the only way I can figure out how to get the additional merchant editable data columns into the report is add them to all the source sheets. This creates a problem because the product developers are not responsible for this info, and I do not want them overriding work that a merchant may have entered via the report.

If there was a way I could create a "master" sheet that pulled in all products (with base product info) from all product sheets, then add all the additional data columns, I could then create reports based on "role" with only those data columns they are responsible for managing. However, this will only work if the consolidated "master" sheet will continually update when products are added or deleted from any of the product developer source sheets. Our assortments are continually changing - managing these changes at the source - with multiple other roles adding data to each item. See attachment.

bringing multi sheet data elements into one master sheet.xlsx

Andrée Starå · 12/15/20

Hi @Jen Henning

I'd recommend using cross-sheet formulas and either VLOOKUP or an INDEX/MATCH combination.

You'd need to have a unique identifier that could be used to link the information together.

Make sense?

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 help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jen Henning · 12/15/20

It might - I have watched the cross-sheet formula tutorials a few times, but am still unclear how any new rows added to the source sheets will automatically feed into the master and remain linked?

Andrée Starå · 12/16/20

@Jen Henning

The simplest method would be to add a so called helper column with the Item# in the destination sheet and then match that against each source sheet. You'd either use a combined formula to show it one column for all source sheets or add multiple helper columns (one for each source sheet) and then combine them to another column.

Make sense?

Would that work?

Jen Henning · 12/29/20

If I am successful in using this method to feed 8 source sheets into one master sheet (unique identifier is item#), when new items are added to one of the source sheets, can they automatically update the master sheet?

Jen Henning · 12/29/20

We have 8 product developers, who each manage their own categories of products (30-50 items each) - they are each managing these products in individual smartsheets and have approximately 30 data points (columns) to manage associated to each individual product.

I then have a technical development team that works with all products from all those sheets, and manages about 50 different additional data points (columns) associated to those products.

Then we have a merchant team that manages a unique set of data associated to the same items managed in the 8 source sheets.

The only way I can find to make this work is to have all columns included in the 8 source sheets and use reports to generate the technical development and merchant team views for editing.

The challenge with this is that I am going to have 300+ columns in the 8 source sheets, the majority of which those sheet owners are not responsible for, nor should they be able to edit.

Ideally, all items in the source sheets could feed into another sheet along with all their corresponding data, and then the additional columns added to the other sheet instead of in the original source sheet.

Clemente Inocente · 04/20/22

Thanks Andrée Starå and your reply sounds like that would work and somewhat makes sense, but can you elaborate on the "Helper Column?" This post also seems like my scenario too and it appear that it's venturing toward a relational database with these sheets, perhaps a primary key is what you're referring to and if you have an example or if anyone else out there does for this matter please chime in. Thank you and to all.

Grace NN · 10/06/22

@Clemente Inocente I am having the same issue. Were you able to figure it out?

Clemente Inocente · 10/21/22

TGIF @Grace NN and thanks for asking. I've done some work arounds & it's still work in progress too. If you're at the design phase, a transactional approach which Cicero does an awesome job explaining an overview that will help (https://smartu.smartsheet.com/intake-to-archive-anatomy-of-a-complete-solution), but if not and you're like me I'm working with existing data so I ended exporting a copy to a historical data/archive sheet and left existing system in place (Intake Form-> Sheet) X like 20+ since I need approval first to create an automation flow that would really help me move completed rows to that historical/archive sheet that I could use later on down the line as a Sheet Summary, Metric Sheet, creating reports, and finally a Dashboarding. If you're going to do those tasks too, let me know and I'm glad to share what I've come up with.

@Andrée Starå , I know you're slammed with questions but if you get a chance could you respond to Grace NN and mine too (see pic below):

Happy Friday all, have a great weekend, and GO SD PADRES!

Reema · 10/26/23

https://community.smartsheet.com/discussion/comment/272054#Comment_272054

Can you please elaborate on how to do this?

Lauryn Vogt · 02/08/24

@Andrée Starå I am working on a similar issue, I have several sheets I want to feed into one sheet and combine into one column. So I am trying to write helper columns to pull the data into the main sheet, then will combine into one column. What I am looking for is to use a formula to find a Matching Item code in a different sheet that is in a particular status.

For example, I have about 100 rows of product in a Sheet. I want to find all the ones in a Status of "To Editorial" and put their item codes in the helper column in my main sheet. Then do that in helper columns for the other 9 sheets and then combine those 10 helper columns to one column. I am unsure of how to write the formula that will pull the item code into the helper column. I haven't ever used an index/match formula and I can't seem to get it right. Any suggestions would be helpful.

How to combine & link multiple source sheets into one master sheet?

Answers

Categories