How to integrate two separate sheets

Hi, I have built two sheets. One sheet is the main source of data and other sheet should be updated automatically when there are changes in the main sheet. I have created an automation to copy changes from the main sheet to the other, but it's just duplicating and creating new rows

Best Answer

  • Lauren Dominique
    Lauren Dominique Overachievers
    Answer ✓

    Thanks for the extra clarity. May I ask why you want two sheets with the same information? Is it so you can share each individual sheet with different teams?

    If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!

Answers

  • Lauren Dominique
    Lauren Dominique Overachievers
    edited 11/08/23

    Without sharing any sensitive information, can you please share more about the general purpose of each unique sheet? Is the goal to have two identical sheets?

    There are a number of ways one sheet can speak to another, so just trying to understand the context before shaping a recommendation.

    If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!

  • Hi Lauren! thank you for responding. Yes, the goal is to let the two sheets have the SAME changes. I tried automation to copy changes based on the trigger and condition but it just creates more and more duplicate rows. For example in the main sheet we have updated the status to complete. What I wanted to happen is to have that same change into the other sheet without adding new entries. We also tried index match but the format is not the same, it just looks like a summary of changes.

  • Lauren Dominique
    Lauren Dominique Overachievers
    Answer ✓

    Thanks for the extra clarity. May I ask why you want two sheets with the same information? Is it so you can share each individual sheet with different teams?

    If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!

  • Yes, to filter information. We have different groups and each group have their own projects. The separate sheet is a way for them to track the project status without the need to see the main sheet.

  • Lauren Dominique
    Lauren Dominique Overachievers

    I thought that might be the case.

    If your organization has purchased Dynamic View, this is the prime use case for that. With Dynamic View, you can create a report-style view that is filtered specifically for the current user. You get to choose which columns are displayed in the view, and which fields can be updated. Best part is that the folks would not need to be shared to the underlying sheet to use it.

    If you don’t have Dynamic View, a good old fashioned Report would be the only way I know how to filter data into specific views for specific teams. Like DV, there is a “current user” filter on Reports so you could hypothetically create just one report with the current user filter applied, & then send everyone the same link to access their unique information. Caveat here is that Reports only work if the person you’re sharing to has access to the underlying sheet.

    I should also note - in order for the “current user” view to work in either DV or a Report, there has to be a Contact Column in the sheet and the person accessing the tool needs to be logged in to Smartsheet.

    Hope this helps!

    If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!