Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
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 '13 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. –  Jo Bedard Apr 29 '13 at 10:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.