Syncing 1 Column On Multiple Sheets

Question

I'm doing due diligence on some acquisitions, and there is too much data that I have to keep track of. I'm thinking that it would work to have one master Google Sheet with multiple tabs where each tab is a different category of data.

However, I'm going to be analyzing hundreds of deals per month, and I want the rows on each tab to be synced. If "Deal A" is on Row 37 of Tab 1, then I want "Deal A" to be on Row 37 of EVERY tab.

The problem is that it's not uncommon for me to delete rows or cut/paste rows, and if I fail to manually cut/paste the same row from all tabs, it's going to wreak havoc on my data. Additionally, if I insert a new row between Row 37 & Row 38, it's a lot of manual work to go through every additional tab and manually insert rows.

SO... is it possible to have a sort of "master" where if I make a change to the master, it will change all tabs?

Answer 1

The spreadsheet layout you describe exposes you to the risk of rows getting misaligned. There is no simple turn-key solution to synchronizing tabs the way you want.

I would recommend that you do not attempt to synchronize rows so that a case always appears on the same row number on all tabs. Instead, assign a unique ID for each case. "Deal A" type identifiers would do fine if you just take care that they are always unique. In the various tabs, refer to the case using the unique ID, and use vlookup() to collect the data in reports across the tabs.

If you are absolutely sure that you want to try and synchronize row insertions and deletions across tabs, the autoInsertAndDeleteRows_ script is one tool that may help. To make it work across all tabs in the spreadsheet, use this parameter:

    // [START modifiable parameters]
    const magicSheets = /./i;
    // [END modifiable parameters]