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.
Answer
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.
No comments:
Post a Comment