Mirror spreadsheet into other spreadsheet

EThomasEVT · 03/12/25

Hi all,

We have a main spreadsheet where we keep all of our projects including the budget for those projects. I would love an option where I can either share that spreadsheet, but mask certain columns (like certain viewers can only see certain columns), or duplicate that spreadsheet, but not fill in the budget columns and then share that spreadsheet. I can't hide/unhide columns as I am not the only person in this spreadsheet, so the columns might not always stay hidden.

I have also tried WorkApps, but when I make a change to the information in the Workapp, it automatically does the same change (like hide columns) in the main spreadsheet, so again (buzzer sound), not an option.

I need something where if I fill out columns A through F, this is automatically shared to another spreadsheet, and that is the version I share with the people to not see the budget. This way, when I make any changes to the "Main" Smartsheet, it automatically updates to the other "mirrored" Smartsheet.

Is there any option like this in Smartsheet's? We normally have to export to Excel, reformat the data and then share, but then this spreadsheet is immediately out of date once we do an update in Smartsheet's that won't be reflected on the Excel spreadsheet.

Any suggestions welcome.

Andrée Starå · 03/12/25

https://community.smartsheet.com/discussion/137207/mirror-spreadsheet-into-other-spreadsheet

Hi,

I hope you're well and safe!

Do the others need to edit anything in the cloned sheet?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

EThomasEVT · 03/12/25

Yes - they will need to add some items to columns and rows that already exist, but not add anything else.

Colleen Patterson · 03/12/25

Lowest barrier of entry on this may be a published report if the people being shared do not need to update the information on the source sheet, but only view.

If they do need to update the data - I have a workflow for our accounting team where with the entry of a single field - in my case job number, the information will port over from all of the other fields. This does require currently the manual entry of the 1 data field, and programming up front to create all of the column formulas to index / match.

Andrée Starå · 03/12/25

https://community.smartsheet.com/discussion/comment/476415#Comment_476415

Ok.

You could use a report and have those extra columns as part of it, but lock the columns they shouldn't be able to edit. You would have to share the sheet with them.

Another option is cross-sheet formulas, INDEX/MATCH, to connect two sheets. In theirs, add additional columns so that users can view the data from the main sheet but can edit/add to the other columns.

Make sense?

Would any of those options work/help?

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

EThomasEVT · 03/12/25

Is there any specific training or video on how to do either one of these - INDEX/MATCH mentioned by Andrée or the Workflow mentioned by @Colleen Patterson?

Andrée Starå · 03/12/25

https://community.smartsheet.com/discussion/comment/476421#Comment_476421

Here's more information.

https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match

This is the simplified structure.
=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0)

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

EThomasEVT · 03/12/25

Thanks, but I'm not sure this is what I am looking for.

To explain again - this is a marketing project, so we have a list of conferences. The columns show a ton of different information - where it is, who is going, who supports it, etc. In this Smartsheet, we also have columns that show the budget. There are a number of people who want to view this sheet. They assign the attendees, or make sure a conference is on the list, where it is, etc. I do not want those people to be able to see the budget. Since we have multiple people in and out of that Smartsheet, it is not as easy as hiding some columns because the next time they view it, someone may have unhidden those columns. Plus, that makes work harder for some people in this spreadsheet.

I want to be able to "mirror" the first 10-15 columns of information into a separate spreadsheet that they can update if need be (with names of attendees, for example), but not have access to the budget information.

How can I make this Smartsheet "copy" the information from my main Smartsheet and only show the data I want to share, nothing else? But if that copy is edited, it will appear in my main Smartsheet.

I'm sorry this is so confusing, which might be why I can't find a solution to this.

Andrée Starå · 03/12/25

https://community.smartsheet.com/discussion/comment/476432#Comment_476432

Your best options would be the WorkApps feature or the Premium App, Dynamic View.

My connected sheet solution would probably also work, but it must be connected from the main to the second. Then, also, from second to primary for the columns you want them to edit, but if you change anything in the main on those columns, it won't reflect in the second.

Make sense? I would go for WorkApp or Dynamic View if you have the budget.

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

EThomasEVT · 03/12/25

We have an enterprise account, so I have access to everything, but as stated above, WorkApps does not work because any edits I make (like hiding the budget columns) in the Workapp, also shows up in the main spreadsheet. WorkApps is just like having a second copy of a spreadsheet. I can't figure out any other benefit to Workapps.

Andrée Starå · 03/12/25

https://community.smartsheet.com/discussion/comment/476436#Comment_476436

In WorkApps, you can add a report just showing what they need and nothing else, and you don't need to share the underlying sheet, so it's like a Dynamic View light solution. (If nothing has changed with the new USM that I'm not updated on, and I have a recollection of something like that was discussed so might not work in WA)

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

EThomasEVT · 03/12/25

I'll see if I can figure it out. Thank you

Andrée Starå · 03/12/25

https://community.smartsheet.com/discussion/comment/476439#Comment_476439

Excellent!

Happy to help!

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Isis Taylor · 03/12/25

@EThomasEVT You may be able to accomplish this with Data Mesh and cell links.

EThomasEVT · 03/13/25

Can you direct me to how I can figure out what DataMesh is and how to use it?

Thank you

Andrée Starå · 03/13/25

https://community.smartsheet.com/discussion/comment/476547#Comment_476547

Adding it here too.

More info on DataMesh and Dynamic View.

https://www.smartsheet.com/marketplace/premium-apps/datamesh

https://www.smartsheet.com/marketplace/premium-apps/dynamic-view

✅ Remember! Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Mirror spreadsheet into other spreadsheet

Answers

Categories