Import Data Using Data Mappings
Data mappings are a flexible way to import data from an external workbook or from a different sheet of the same workbook into a WIP Report. Data mappings are saved references between the WIP report fields and any cell ranges in a custom sheet. The data mappings are saved together with the WIP workbook they belong to. Data mappings are typically most appropriate for on demand or recurring imports of data into the same workbook.
Data mappings can become reusable across multiple workbooks if you save them as templates. For more information, see Import Data Using Templates.
Data mapping setup
To set up a data mapping, follow the instructions below:
1.Open the workbook which contains an existing WIP Report or create a new WIP Report.
2.Click the Data Mapping tab in the WIP Report Pane.
3.If you want to import values in the WIP In Process sheet, click the In Process tab. To import values in the WIP Completed sheet, click the Completed tab.
4.Enter a source cell range next to the field where data must be imported. You can either type the cell range manually or click the 
button and select the required cell range from a source sheet. In the example below, the field Contract Name is mapped to the source cell range \$B\$4:\$B\$8 from a sheet named Sheet1 (see screenshot below).
You can also map cell ranges from Excel files other than the current workbook. The same instructions as above apply, with the only requirement being that both the source and target Excel books be currently open. In the example below, the field Contract Name is mapped to the source cell range \$B\$4:\$B\$8 originating from a sheet named Sheet1 of a separate Excel book called Book1.
| Note: | The source cell range must consist of either a single row or a single column. Values can be copied or referenced from other workbooks or sheets as long as they represent valid Excel cell ranges. It does not matter whether the range is vertical or horizontal in the source sheet. In the sheets WIP In Process and WIP Completed, the imported cell ranges will automatically conform to the required layout. To find out more about creating references to cell ranges, see the Microsoft documentation. |
5.Repeat the previous step for each WIP report field that must be mapped to a source cell range.
Once you set up the data mapping, you can copy or reference the actual values.
Copy or reference mapped cell ranges
There are two ways to import values from a source sheet: copying or referencing them. Copying means that the value is literally taken from the source range and placed into the target. If the source value changes, it is not automatically updated in the target WIP sheets. However, you can update the WIP report from the source at any time by clicking the Copy Values button. Referencing means that, instead of storing an actual value, the WIP sheet will contain a formula which refers to the source cell range. Updating the value in the source will also automatically update it in the target sheet next time when you open the target sheet.
To copy or reference the mapped cell ranges, take the steps below:
1.Click the Data Mapping tab in the WIP Report Pane.
2.Click Copy Values or Reference Values (see the explanation above for the difference between these two options).
3.When prompted that you are about to overwrite data in the WIP report, click OK to confirm.
When you click the buttons Copy Values or Reference Values, values are copied or referenced, respectively, from all data mapping tabs (In Progress and Completed), not just the current visible tab. If you copy or reference values from another Excel book, both books must be open for the values to be copied or referenced correctly.