3

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.
Improve this question
6
  • Is your data in named excel tables (select a range, and go to Insert Tab and click Table)? You can reference columns of a named table in formulas easier than remembering cell references, and do matches using the INDEX() and MATCH() functions...
    – N West
    Commented Feb 12, 2015 at 17:58
  • @NWest Named tables probably is one option to make things easier. The problem with using things like VLOOKUP or INDEX/MATCH though is they're pull-only operations. I need to be able to push and export as well. I'm not expecting to be able to do both at once, but I do need to be able to do each as needed without overhauling the sheets every time or leaving a lot of "guts" (e.g.: otherwise-temporary helper columns) exposed all the time.
    – Iszi
    Commented Feb 12, 2015 at 18:03
  • Yeah... It sounds like you need to move from excel to a relational database of some kind. Update-able views for the "child" data, which push the changes back to the underlying master table would solve your problem nicely... but that's a single system solution which may not be feasible in your environment. :-/
    – N West
    Commented Feb 13, 2015 at 14:58
  • possible duplicate of How do I merge data from other excel files to one master excel worksheet?
    – agtoever
    Commented Mar 11, 2015 at 9:49
  • See (this question](superuser.com/questions/776116/…) and answer. I think editing in multiple sheets and aggregate them to one master using Power Pivot is the best way to go.
    – agtoever
    Commented Mar 11, 2015 at 9:50

1 Answer 1

Reset to default
1

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .