Duplicating a column of data from one sheet into another

Richard Heath

We run a Master Sheet containing many rows (Projects) and columns (works required). We would like to share a smaller number of columns to an external party.

Normally I would create a Report, select the necessary rows and hit Publish, however, this doesn't permit updates to the Report and therefore the Sheet by the external party.

To overcome this, I thought I could create a new Sheet, which I would share, that only pulls particular columns of data across from the Master Sheet - effectively duplicating a sheet automatically, but constrained to only a smaller set of data.

Can anyone suggest a way of automatically copying data from one sheet's column to another? And as new data is added to the Master, it automatically appears in the new sheet? I assume a MATCH or VLOOKUP, but I can't get it to work.

Thanks,

Richard.

Mark Cronk

Hi Richard, Bi-directional synchronization of sheets isn't possible. You could use an update request automation to send specific rows and columns to an external party. Once used, the link becomes dead. Does that work for you?

Mark

Richard Heath

Hi Mark,

Thanks for the response. I think I have actually resolved it.

I've utilised a VLOOKUP to pull data from the Master over to the new Sheet. I tag each of the VLOOKUP columns as a Column Formula and then lock it off in the new Sheet. Columns adjacent to these are drop-down selections and once published, an external party can update as required. As you've noted, these aren't bi-directional, so we will need to transfer any of the external party's comments back to the Master Sheet manually.

I'm happy with this outcome, as it protects the integrity and privacy of the original sheet....I hope.

Andrée Starå

Hi @Richard Heath

I hope you're well and safe!

I'd recommend using an INDEX/MATCH structure instead because it's more secure and versatile.

Which Smartsheet Plan do you have? If you have Enterprise or higher, you could use the new WorkApps feature and reports as you need without sharing the underlying data.

What do you think?

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!

Mark Cronk

Hi Richard,

Glad you found a solution that works for you. @Andrée Starå provided another great option if too.

Thank you for contributing to the Community.

Mark