Creating Excel Templates to Update Metrics in a Single Project

The procedure below describes how to add metric fields to an Excel template that display and update values when published to Accolade for a single project. A single template can also contain project metadata, matrices, and metrics in related projects.

To setup an Excel template that updates metrics in a single project:

Note:  The following procedure assumes you are familiar with basic Excel functionality.

1. Open the Process Document - Smart Excel <version>.xltx file and save a copy to your computer, using a file name that clearly identifies the purpose of the template.

Ensure that the master Process Document - Smart Excel <version>.xltx file is kept in a safe place, such as the template library, with no modifications to it.

2. (Optional) Hide the SGM_Metadata, SGM_RelatedProjectMetrics, SGM_DeliverablesActivities, and SGM_Notes worksheets, as none are required for metrics updates in a single project; however, do not delete them.
3. On Sheet1, create the display worksheet that users of the template interact with, identifying and noting the purpose of the cells throughout the layout. More...

   • Cells that only display data from Accolade. This can be metric data or project metadata from Accolade.
   • Cells that only upload metric values to Accolade.
   • Cells that display a metric value when the document is first opened, and upload any changes to the value to Accolade.
   • Cells that contain information and data that is not sent to or received from Accolade. For example, label cells.

Enter all the labels, column headings, background color, formulas, and so on, into the cells that do not receive or send data to Accolade to verify that the worksheet looks as you intend it to for users.

It can be useful as you are laying out the display worksheet to name the upload and download cells to more clearly identify them in the layout prior to entering the field codes to establish the data connection with Accolade.

You can add additional display worksheets, as necessary.

4. Add a new worksheet and enter the Accolade field codes that represent the data to display within the display worksheet.

Do not leave blank rows in the list of codes.

5. (Optional) Rename Sheet1 to an appropriate, meaningful name for the display worksheet for users completing the template. For example, Post-Launch Review Scorecard.
6. Create cell references between the display worksheet (originally Sheet1) and the worksheet added in step 4. More...

    

   Cell Purpose on Display Worksheet	Instructions
   Cells that only display downloaded data from Accolade	On the display worksheet, create a cell reference for each cell that displays data to the appropriate cell on the worksheet that contains the field codes. Protect and lock the cells on the display worksheet that contain references to the field codes worksheet.
   Cells that display a metric value when the document is first opened, and uploads any changes to the value to Accolade	For each cell in the display worksheet that displays a value when the document is first opened, and uploads any changes to the value, enter a metric field code that includes the #OnceOnly option. For example, {*METRIC:NPV #OnceOnly*}.

7. For each cell in the display worksheet that uploads a new metric value to Accolade, complete the columns in the SGM_MetricTransfer worksheet. More...

   • Metric System Name - Enter the system name of each metric that is updated using the template.
   • Metric Display Name - Enter the display name of the metric that is updated using the template.
   • Current Value String - The current value if the metric is a String or Long String type.
   • Current Value Number - The current value if the metric is a Number type.
   • Current Value Date - The current value if the metric is a Date type.
   • Data Type - The metric data type. Acceptable values include String, Long String, Number, List, Multi-Select List, and Date.
   • List Values - The acceptable values if the metric is a List or Multi-Select List type.
   • Publish will use New Value column - Enter Y in a metric row if the value entered for this metric in the display worksheet uploads to Accolade or enter N if the value is only displayed in the worksheet.
   • New Value - Enter cell references to the cells in the display worksheet that users use to enter new metric values. Do not type anything into this column after entering the cell reference.

   Do not place any intermediate formulas in the SGM_MetricTransfer worksheet, do not delete the first two rows, and do not rename the worksheet.

8. Protect the display worksheet.

Protecting the display worksheet keeps users from overwriting cells that contain references or formulas.

Important! Do not protect the entire workbook. The SGM_ worksheets must remain unprotected for the download and upload between Accolade to work correctly.

9. In the display worksheet, remove the lock on the cells that users use to update metric values.
10. Hide the SGM_MetricTransfer worksheet and the worksheet you added that contains the list of field codes, as these are worksheets that users interacting with the template do not need to see.

Prior to saving, select the cell in the display worksheet where a template user typically starts typing in the template to default the cursor to that cell when the template is opened.

11. Save the file and add the template to the Template Library.

Notes:    Sopheon recommends that all SGM_ worksheets should be hidden prior to publishing for use in a process model, to prevent users from making inadvertent changes. Do not rename these worksheets, and do not delete them from the file.