Importing 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 are part of and they 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, see Importing Data Using Templates.
To set up a data mapping:
1.Open the workbook which contains an existing WIP Report, or create a new WIP Report (see Creating 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 originating from a sheet named "Sheet1".
You can also map cell ranges from Excel files other than the current workbook. The same instructions as above apply, the only requirement is that both the source and target Excel books are 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 if the range is vertical or horizontal in the source sheet; in the "WIP In Process" and "WIP Completed" sheets, the imported cell ranges will automatically conform to the required layout. For examples of creating references to cell ranges, see https://support.office.com/en-US/article/Create-or-change-a-cell-reference-C7B8B95D-C594-4488-947E-C835903CEBAA.|
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.
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; also, if the source value changes, then it is not automatically updated in the target WIP sheets (you can however update the WIP report from the source at any time by clicking a 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:
1.Click the Data Mapping tab in the WIP Report Pane.
2.Click either 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.
Note the following:
•When you click the Copy Values or Reference Values button, values are copied (or referenced) 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 values to be copied or referenced correctly.