How to automatically transfer cells from multiple sheets to one master worksheet in Excel
I have a workbook with one master sheet, which lists all combined assets, and several sub-sheets (listing tangible assets, securities, etc.). For the sake of simplicity, assume the master sheet includes two columns: Name and Value. The sub-sheets include additional columns that don't need to be transferred to the master sheet (e.g., acquisition date, basis, etc.). For each new entry on an individual sub-sheet, I would like to insert a new row on the master sheet and add the relevant data to the cells.
Name Value Acquisition Date Basis AAPL $450 1/8/2010 $211 GE $2,500 11/1/2011 $1,500
Tangible Assets Worksheet
Name Value Insured Jewelry $6,000 Yes China $1,200 No
Master Worksheet - List of Combined Assets:
Name Value AAPL $450 GE $2,500 Jewelry $6,000 China $1,200
Can this be accomplished with an Excel formula, or will VBA be required?
Any suggestions on the best way to approach this would be much-appreciated.
This can be achieved using Get External Data (note, in spite of the name, this can be used to self-reference in a workbook)
This answer is based on Excel 2010. If you are using a different version it's still possible, but menu's to access these features may be different.
- In the Master worksheet, from Data tab, select Get External Data / From Other Sources
- Select From Microsoft Query
- From the Select Data Source dialog, select Excel Files*
- From the Select Workbook diaog, select the file you are working in
- Microsoft Query should now be open, displaying the Add Tables dialog
- Add Securities and Tangible Assets sheets
- From Tangible Assets, double click Name and Value to add them to the query
- Select SQL from the menu bar
Edit the displayed query to this, and accept:
(SELECT `'Tangible Assets$'`.Name, `'Tangible Assets$'`.Value FROM `Securities$` `Securities$`, `'Tangible Assets$'` `'Tangible Assets$'`) UNION (SELECT `Securities$`.Name, `Securities$`.Value FROM `Securities$` `Securities$`, `'Tangible Assets$'` `'Tangible Assets$'`)
You will get a warning, SQL query can't be displayed graphically. Select OK
- Select Return Data to Excel from File menu.
- Select where to place the query
- It's Done!
Now, whenever your source data sheets are updated, refresh the Master query to get the latest data.