Metrics Management requires a named range in Excel to export data from Metrics Management and import data from Excel. The system specialist creates the named range in Excel, which Metrics Management recognizes as a database table, and specifies the range in a Metrics Management data export.
Before creating the named range, identify the number of columns specified in the Metrics Management data export. For example, by default, a Metrics Management user data export specifies five data columns: Measure Name, Location Name, Comparative Name, Period, and Data. When creating the named range, specify the same number of columns in Excel as the data export contains.
The system specialist defines a single row in the named range. During export, the range dynamically adjusts to accommodate the number of records in the export. For example, the system specialist creates a named range of $A$2:$E$2 for an export that contains five columns. If the export contains ten records, Excel adjusts the range to $A$2$E$12.
In cells to which numeric data imports, format the cells as Number. Otherwise, during the import, numeric values convert to strings and require using the VALUE() command to convert each record back to a number. Do not format an entire column as Number, or numbers will also convert to string values during import.
How to create a named range in Excel 2007
1 Create a new Excel file. Optionally, rename the worksheets with meaningful names, for example, rename one sheet to Actual and another to Budget.
2 In Excel, in one row, select the same number of columns as specified in the Metrics Management export. For example, select columns A through E in row 2. Do not define the named range in the first row. Doing so creates additional tasks due to formatting restrictions.
3 Choose Formulas➛Define Name.
4 In New Name, type a name for the range. For example, name the range Export_Actuals, as shown in Figure 7‑1. Then, choose OK.
Figure 7‑1 Naming a named range
5 To format a cell to accept numeric data, perform the following steps:
1 Select a cell. For example, select E2.
2 Right-click and choose Format Cells from the context menu.
3 In Format Cells—Number, in Category, select Number. In the Decimal places increase or decrease the value. For example, increase the number of decimal places to 4, as shown in Figure 7‑2. Then, choose OK.
Figure 7‑2 Formatting cells which accept numeric data
