Using a sheet as a master for other sheets

Hello, we have a 'Current Employees' Smartsheet which lists all employees with certain information such as Start Date, Contact Details, etc. If an employee finishes we enter a Finish Date and this then automates them (that row) being moved to another sheet.

What we are trying to achieve is to use the above 'Current Employees' sheet as a master document or database for other sheets where we need to show a list of our current employees, e.g. training records. We don't need all the info from the main sheet but we do need to be able to add columns in so a Report won't work in this instance. I don't think cell linking will work either as it would mean having to relink all sheets if someone starts or finishes. Does anyone have an idea for how to achieve this?

Find more posts tagged with

Sheets

Accepted answers

All comments

Andrée Starå

Hi @Emmet McKenna

I hope you're well and safe!

Do you happen to have access to Data Shuttle, or is it an option to get it? It can be used to "sync" dropdown lists.

Would that work?

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!

Emmet McKenna

Hi Andree,

Thanks for the suggestion, I'm not if we have access to Data Shuttle or not but we do use an plugin called DDUpdate for syncing Dropdown lists. This isn't really the function we are trying to achieve here though, ideally want we want is for the rows to be duplicated across sheets for certain columns, e.g. Employee Name. If a employee then leaves the company and the master sheet is updated that all sheets filtering from it also update automatically?

Andrée Starå

@Emmet McKenna

Happy to help!

Ah ok. I misunderstood!

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet and then copy/move the row from that sheet to the main destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work?

✅Remember! 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!

Emmet McKenna

Hi Andree, I've been looking into the INDEX/MATCH function for this but am struggling to get it to work. See attached examples of what I am trying to achieve:

The Master Sheet contains the list of Employees, Department, Start Date and Finish Date. What I am trying to achieve is that any Employees showing in the Master Sheet feed to other sheets, e.g. the Feeder Sheet but that this is based on 2 criteria.

When a new Employee is added and the Start Date is Not Blank they shown in the Feeder Sheet
When an Employee finishes and a Finish Date is added (i.e. Not Blank) and this would update the Feeder Sheet again to reflect this and not show that Employee in the Feeder Sheet (I've just copied and pasted in the attached example).

Master Sheet.jpg

Feeder Sheet.jpg

Emmet McKenna

Hi @Andrée Starå just checking to see if you had a chance to look at this? Thanks

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.