Updating multiple sheets at once - team management

AnneSophie

I use different sheets to keep track of different types of information on each team member (HR info, work assignment, invoicing...). Because of the nature of the work, there is a high turnover in the team, so I need to constantly update rows (add, remove, change status). Is there a way to connect several sheets where the first few columns would be sync'd and could be updated in any of the sheets.

Andrée Starå

Hi @AnneSophie

I hope you're well and safe!

There are some options.

• Cross-sheet formulas
• Cell-linking
• Premium App, Data Mesh

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

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!

AnneSophie

All sheets include last name, first name, status (active, inactive, processing), Language, email 1, email 2, role.

Then, HR sheet includes several columns allowing me to keep track of which tools they were given access or need access to.

The assignment sheet has columns to keep track of which task # was assigned to each team member. They can be assigned several task #.

The invoicing sheet allows me to keep track of what was already invoiced and paid.

I need to be able to update the information mentioned at the top (add a row, change a status) and have it update in all sheets.

Andrée Starå

@AnneSophie

Ok.

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.

You can use the Auto-number feature on the main sheet, add the numbering on all other sheets, and then use that to match the information from the main sheet to the others.

Make sense?

AnneSophie

Yes, thank you. I am not yet familiar with these functions but will look them up and learn. Thanks again.

Andrée Starå

@AnneSophie

Happy to help!

The structure of an INDEX/MATCH would look something like this.

=INDEX({ColumnWithTheValueYouWantToShow}; MATCH(CellThatHaveTheValueToMatch@row; 
{ColumnWithTheValueToMatchAgainsTheCell}; 0))

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

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