Single source of truth from two sheets of shared data

I've been tasked with creating a workflow or function that lets Company 1 add rows (sales leads) containing simple client / salesperson data, and status of the lead. Company 2 would then access a sheet with this same client data, but only be required to change some drop-downs / text related to status.

I started with two separate sheets of identical columns and workflows to copy row data from Company 1 to Company 2s sheet, but then ran into the issue of Company 2s updates creating duplicate entries for Company 1 when 'passing' their changes back.

Can someone help me understand the best approach to implement a function like this? My worry is also ensuring a proper single source of truth for reporting. Would it make sense for Company 1 to use the data shuttle and import from an excel file instead when creating entries, then exporting / offloading the data containing updates / completes?

I feel like there can be many ways of varying efficiency to implement this and more importantly don't want to hand my director a frankensheet that could have been much more intuitive. Looking forward to hearing how others would tackle this!

Answers

  • Protonsponge
    Protonsponge ✭✭✭✭✭✭

    Hello @IanPM - Do you have access to Dynamic View by any chance? You could have your underlying source sheet feed in to a report that details only the columns/information that is relevant to them.

    You could then make use of Dynamic View to allow each company to only see, edit and update the field that you would like them too.

    I find this feature a key part of my daily work that enables me to get updates from multiple parties into a single set of sheets within a project. I have a set of Dynamic Views that I push out of individual functions and in each case, I can set the fields they can can see and edit and even make some fields mandatory during an update.

  • Thank you, @Protonspounge! Watching the video I kept saying to myself, this is exactly what I need! But it looks like an added tier package to Smartsheet, if right in that assumption, is there any roughly equivalent workflows / functions I can use to serve similar purpose, maybe with some limitations?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!