How to create a source sheet that automatically updates other sheets if data is removed?

Our organization has roughly 150 program participants and we have a number of different sheets that have each participant listed as a row with various data in columns. I am curious if anyone has experience with creating a source sheet of contacts so that if I add/remove a participant from the source sheet, they get added to all subsequent sheets.

Currently part of our process when adding/removing participants requires us to go to numerous sheets and make sure they are removed so I am hoping to automate that if possible.

Any help is appreciate, thanks so much!

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Leibel S

@Kishan Naik RA

You could use copy row action for adding them to all the sheets

For removing you could make a lookup in every sheet to the main source sheet and if it does not exist there it checks a check box. Then run an automation (once a day) that moves rows that are checked to a 'delete sheet'

Andrée Starå

Hi @Kishan Naik RA

I hope you're well and safe!

You could use a so-called Master Sheet and combine it with cross-sheet formulas. It would keep all the other sheets updated.

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 support the Community 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!

Kishan Naik RA

Thanks Andree and Leibel! Andree I have thought of using a Master Sheet that would have the list of program participants but I am unsure on what the correct cross sheet formula would be to pull a newly added row from the Master Sheet into all other sheets where that individual needs to be added.

Would you be able to share a quick example of what a formula like that would look like?

Thanks again for your help

Paul H

@Kishan Naik RA

It wouldn't be a cross reference formula but rather copy row automation that triggers when a new row is added to the master sheet.

https://help.smartsheet.com/articles/2479626-automatically-move-or-copy-rows-between-sheets

Andrée Starå

@Kishan Naik RA

Happy to help!

My suggestion would be to use an autonumbering column in the master sheet and then add so-called helper columns in the other sheet where you'd add the numbers 1,2,3,4, and so on as needed, and then we'd use that to match the rows together.

Make sense?

Would that work?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.