Excel-to-Excel

Excel-to-Excel Updates

Update data in destination workbooks based on data in source workbooks.

  • Reliably update hundreds of ranges and tables in seconds
  • Use links that don't break when you change file names or location or when you share with others

“Excel-to-Excel Updates” is a feature included in the Excel-to-Word Document Automation Add-In.
 

Excel-to-Excel Document Automation

 

“Excel-to-Excel Updates” enables you to: 1) submit values from named ranges and tables in a source workbook. Then, 2) update data in matching named ranges and tables in a destination workbook.

Submit values from named ranges and tables in a source Excel workbook. Then, update data in matching named ranges and tables in a destination workbook

Key Benefits

  • Save time and avoid errors
    • Avoid manual copy/paste
    • Update hundreds+ of items in seconds
  • Improve reliability
    • Use robust links that don't break when you change file names and file location
    • Share workbooks with others
  • Simple to use
    • “Links” are named ranges and tables
    • The add-in makes it easy to create and maintain the links

 

Use Cases

There are three types of use cases: summarize results from a details workbook to a summary or output workbook; update multiple Output Workbooks from a single calculator workbook; or consolidate multiple workbooks into a single workbook. 

 

Summarize Results

  • Summarize results from a details workbook to a summary/output workbook

 

 

 

Update Multiple Output Workbooks

  • Import data from a reference workbook into multiple output/calculation workbooks
  • Create multiple customer-specific output workbooks from a proprietary/detailed configurator workbook

 

 

Consolidate Workbook Data

  • Consolidate content from multiple workbooks (or multiple contributors) into a single workbook
  • Financial consolidation: for example, consolidate budgets from multiple departments

 

 

How It Works

Link the workbooks once, then update many times

  Link Workbooks Once Update Many Times
Source Workbook

Name ranges/tables starting with a "Item Name Prefix" that you want to export. 

click "Submit Content"

Destination Workbook

Name ranges/tables starting with an "Item Prefix (Destination)". For example, the prefix could be "in_", but it could be the same as the source prefix.

The item's name should match a source item's name. For example, r_ItemName (source) will update in_ItemName (destination).

Use "List Items" to view which ranges/names are matched to source items and to help add items that are not yet added.

"List Items" to list which ranges/tables will be updated

"Update Excel" to update all matching items (where destination item name matches source item name)

Please Note: Excel-to-Excel updates values only (not formulas or formats)

Excel-to-Excel does not update formulas: the location of dependent cells can change from source to destination. For example, if user inserts a row in one of the files. Another example: the source range may contain a formula referring to a cell on another sheet that may not exist in the destination file. There's no way the add-in can determine the desired target cell location in the destination workbook. That is why the add-in only updates values. The add-in also allows you to optionally skip updating cells that contain formulas (that may already refer to desired cells in the destination file).

Excel-to-Excel does not update formats: You must format your destination range/table as desired. An advantage of tables is that table styles are applied when you re-size tables. If ranges are re-sized, formats will not follow.

 

 

Options

Item Prefix                                    
  •  If the "Item Prefix (Destination)" is blank or the same as the source "Item Name prefix", then destination item names must match source names exactly. For example, r_ItemName (source) will only update r_ItemName in the destination.
  •  The destination prefix can be different from the source prefix. For example, if the Destination Item Prefix is "in_", then r_ItemName (source) will update in_ItemName (destination).
  •  Having a unique destination prefix can help avoid updating items that may be intended for updating Word/PowerPoint reports. You may also prefer to have unique source prefix, for example "out_" or "ex_" (for export) to separate items intended for reporting from items intended for Excel-to-Excel updates.
Skip Cells with Formulas
  • "Optionally skip updating formulas (any cell starting with '=') in the destination. For example, you can avoid overwriting total/subtotal formula with values in the destination.
  • You also may have cells that you want to contain values that come from other locations in the destination workbook, not the source."
  • CAUTION: if you don't have "Skip Cells with Formulas" checked, ensure you don't click "Update Excel" in your source workbook: it will overwrite all of your formulas in named ranges/tables with values.
Resizing
  • If checked, destination ranges/tables will be resized (e.g. expanded/shrunk) to match the size of the source item. 
  • "It is more common to allow resizing of tables because the table styles will move with the re-sized table. 
  • Ranges can be re-sized, but formatting will not follow re-sized ranges."
  • CAUTION: ensure ranges/tables don't expand over (and overwrite) existing content.
Hidden rows and columns
  • In the source workbook: If you filter or hide rows and you only want the visible rows to be transferred to the destination: ensure the source item has the "Include hidden rows & columns" property (List tab, under Manage Linkable Content) set to "No".
  • In the destination workbook, it will always update all rows (visible and hidden). The "Include hidden rows & columns" property has no effect.

 

Next Steps

Add the Excel-to-Word Document Automation Add-in. See how HERE

  • It is easy and safe to install from the Microsoft Store from within Excel
  • You can try it for free – no registration required. Optionally sign in to increase usage.

"Insert Sample Content" from the Start tab of the add-in to try out a real example. See the “E2E” worksheet.

Link and update your own workbooks