Excel – Tracking & Synchronization of Multiple Sheets with a Master Sheet

microsoft excelmicrosoft-excel-2013sync

I often run into situations where information on a spreadsheet needs to be broken up and distributed to separate organizational units, but at the same time I need to be able to comprehensively track and report updates for that information as one list.

Is there an efficient way to automate the key tasks needed for this?

  1. Break one master sheet into multiple separate files, based on a given attribute.
  2. Synchronize information between the master sheet and all related files.

Each spreadsheet will be identical, in terms of column headers and layout. Most of the time, line items will not be added/removed over the course of tracking the data. Generally, I can also assign a GUID to each line item when I need to be able to cross-reference data. While it would be ideal for the solution to automatically adapt to exceptions, they should be rare enough that I can handle those manually as needed.

I'm fairly familiar with writing Excel formulas, and often will end up using complex VLOOKUPS or similar operations for stuff like this (when I'm not just wholesale copy/pasting between sheets manually). I'm open to learning things like VBScript for this, but have practically zero background in the language.

Perhaps this is a task more suited for a database, like Access, and I'm open to such options as well. But in the end my output needs to be in an Excel spreadsheet and I need to be able to easily synchronize the database with those documents (both ways) when there are updates.

I'd prefer to do this without any third-party software, if reasonably possible.

Unfortunately, the processes involved here are usually rather ad-hoc. Reports are often just sent back and forth via e-mail. Occasionally, we do use file shares and/or Sharepoint. I need to be able to adapt as much as possible, without forcing everyone to one specific system all the time. (While it would be ideal, it's a problem beyond my authority to solve.)

Something similar to this would be good:

  1. "Master Sheet" (or database) resides in my own little corner of the world (file share, Sharepoint, desktop, wherever).
  2. "Master Sheet" exports "Child Sheets" to a defined location, wherever I need them to be. All children may or may not be stored in the same location.
  3. "Child Sheets" are either updated directly at the linked location, or I will manually copy the latest revisions to the linked paths prior to synchronization.
  4. "Master Sheet" then needs to be able to push/pull updates to/from linked "Child Sheets" on-demand.

Best Answer

Excel can't sync 2-ways
As I explained in this similar (but not the same) question, Excel can't sync data two ways. Either you have one master and several slaves (or childs, as you call them) and you can edit the master, but only read the slave. Or you do it the other way around: you can edit the slaves, but you can only read the master.

Another thing Excel can't do (out of the box) is to push data. Excel is designed to sync data "on demand" or "just in time", whenever you open a file or update it's contents. You can't push an edit to another Excel sheet.

If the above are "must have" requirements, Excel isn't the right solution, buy you should look at a solution that is based on a relational database.

I'll briefly explain how to setup the master->slaves and the slaves->master setups, just to get you started.

Master->slaves
Create a table in the master sheet and save it; you could also use a Microsoft Access database for this. Next, create a pivot table in each slave. Set the pivot table filter on the required settings and save the sheet.

Slaves->master
Create a table in each slave. Use Power Pivot to aggregate the different slaves and put them in the master. This solution would also work for a "distributed" approach, in which there is not one master, but all salves connect to each other. That will require two worksheets in each workbook: one with the data (the subset that is managed by that user/department) and one with the pivot report based on Power Pivot, which contains an aggregation of all the data in the different slaves.

Maybe that last mentioned option (the distributed approach) comes closest to your requirements.

Note that if you need to hide a subset of the data on one sheet, you can always put a pivot filter in place and lock the report filter cell.

Related Question