Forum Discussion
Can I create a MASTER WORKBOOK that updates from different workbooks?
I am looking to create a master workbook from different workbooks that I currently have. I've seen answers for workbooks/sheets with actual data and equations, but I do not have "data" as in numbers or formulas in mine, just lists of accounts and customers and their account numbers, etc. I want to create a workbook, AND NOT just multiple SHEETS in one workbook, that automatically updates when I make a change in, say, the workbook with the sheet for "Brooke's OQ" that updates on my main workbook with the sheet for "Brooke's OQ" as well so I don't have to make that update twice. Any suggestions???
5 Replies
Hi. I am looking for the opposite of this.
Please could you help me.
I have a table of contents (a summary page) - There are different items on there which I need to create separate worksheets for.
The issue is, for each of the sections there is a set sheet I need to use and I want each of the new sheets to copy this. I have a bit of a visual:
Contents: Number Section 1 Outside 1 1.1 Garden 1 Section 2 Inside 14 2.1 Door 4 2.2 Window 10 What i have is Section 1.1 - Garden (this has its own set sheet which has places for images and some standard information regarding works there) there is only 1 of these to create, so i don't need another sheet. But if i did i would need excel to automatically copy this sheet by the number in the last column and create worksheets 'Garden 1, Garden 2, Garden 3....... etc)
Same for Door - I will need this to use 2.1 as the first sheet and then copy it 4 times using Door 1, 2, 3 etc.
and so on...
I would also like all the 'sections' to have the same data in the top few Rows - which will be the customer and location information.
once the sheets are created I can then convert it all into a PDF or Power point presentation.
Please can anyone tell me if this is possible and how to do it.
So far i have tried the developer function, but i am not sure i am doing it correctly and the Macros are blocked, and will not unblock on this computer.
Any help will be really appreciated.
- real time update? or consolidate all sub sheets to master sheet?
- Real time for multiple users?
If yes,I am afraid impossible.
One alternative way is all records in one sheet and filter on user column with different user using on online excel or another web application like https://club.excelhome.net/forum.php?mod=viewthread&tid=1520437&mobile=
As a proposal, you can create a master workbook that updates from different workbooks using external references or Power Query (Get & Transform Data) in Excel.
Here are two approaches you can consider:
- External References:
- Open your master workbook and create a new sheet for each workbook you want to link.
- In each sheet, use external references to link to the specific ranges or cells in the source workbooks. For example, if you want to link to a cell in the "Brooke's OQ" workbook, you can use a formula like ='[Brooke's OQ.xlsx]Sheet1'!A1.
- Whenever you make changes in the source workbooks, the linked cells in the master workbook will automatically update.
- Power Query (Get & Transform Data):
- Create a new query for each workbook you want to link to in the master workbook.
- In each query, connect to the source workbook and select the necessary data.
- Apply any transformations or filters as needed.
- Load the query into the master workbook.
- Whenever you refresh the data in the master workbook, it will update the data from the source workbooks.
Both methods allow you to create a master workbook that automatically updates when changes are made in the source workbooks. Choose the method that best suits your needs and the complexity of your data.
- External References:
