I have a master workbook which I use to store all the client information I have available but I also need to generate other spreadsheets (preferably in separate files) with sub sections of the data in the master spreadsheet.

Basically I want to set up a template that will automatically update as data is added, deleted or changed in the master spreadsheet. Up until now I've just been linking fields manually but that doesn't help when data is added to the master spreadsheet as you then need to add the links again by hand.

Does anyone have any suggestions for the best way to solve this problem at all? I'm using Excel 2007 if that makes a difference. I'm also open to the suggestion of using VBA to accomplish this but if there was an easier method it would preferable as this spreadsheet will have to maintained by people who do not know how to use VBA.


1 Answer 1


Have you tried using a pivot table ? Particularly, if your master sheet data is set up using the list command, the slave sheets will update and add records whenever they're open or on command ("actualize all" in the pivot table tab). Setting up the pivot table as you wish them to behave and format them is a bit of a pain on xl2007, but once it's done you won't have much to do in maintenance term.

  • Good plan. I hadn't thought of that. I'll have a play around with pivot tables when I am at work today.
    – Cromulent
    Apr 29, 2013 at 5:58
  • 1
    The trick, if you're not doing any calculations on your data, is to put everything in the page filter and row/line areas of the pivot table. You can leave the column and value field empty.
    – P. O.
    Apr 29, 2013 at 10:50

You must log in to answer this question.

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