How do I create a dynamic Master Document in Excel

Question

I have one document in excel that has a list of product with corresponding codes. 60 stores all have a copy of this excel sheet and they enter data in cells next to the product. I want to link all these sheets so than if I add a new row and product to the "master document" right in the middle it will add that row or every other sheet with whatever format I am using while moving the data they have entered to keep with the corresponding item they entered it for. Basically I add a new row in my document and it creates a new row in a corresponding document.

Answer 1

You have two approaches if you want to restrict yourself to Microsoft Office.

The "traditional" approach is to create a Microsoft Access database that links to all the spreadsheets. You then create a View that combines all of the data.

If, however, you are using a new(ish) version of Excel and are able to install addins, you can also do this very easily using Microsoft's free PowerQuery addin.

With powerquery, you create a merge query that pulls all the data from the various spreadsheets and merges them together, this can then be further filtered, cleansed, transformed and output as an Excel "master" table.