How to automatically transfer cells from multiple sheets to one master worksheet in Excel

0
votes

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.

Securities Worksheet:

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.

Thank you.

1
vbaexcel

1 Answers

2
votes

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.

Steps:

  1. In the Master worksheet, from Data tab, select Get External Data / From Other Sources
  2. Select From Microsoft Query
  3. From the Select Data Source dialog, select Excel Files*
  4. From the Select Workbook diaog, select the file you are working in
  5. Microsoft Query should now be open, displaying the Add Tables dialog
  6. Add Securities and Tangible Assets sheets
  7. From Tangible Assets, double click Name and Value to add them to the query
  8. Select SQL from the menu bar

  9. 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$'`)

  10. You will get a warning, SQL query can't be displayed graphically. Select OK

  11. Select Return Data to Excel from File menu.
  12. Select where to place the query
  13. It's Done!

Now, whenever your source data sheets are updated, refresh the Master query to get the latest data.