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???
- 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:
