Creating Excel Templates to Update Deliverables and Activities

Note that team members cannot update deliverables and activities that are conditioned to hide. Additionally, activities cannot be added to hidden deliverables and hidden activities cannot be deleted. Refreshed templates respect deliverable and activity conditions. See Conditioning Deliverables and Activities Overview.

The following procedure provides basic information about how to setup the template. The specific document and its function dictate the formulas and other operations to populate the template. A single template can also contain project metadata and metrics. A single template can also contain metrics, project metadata, and metrics in related projects.

Important! The template cannot add or delete deliverables.

To setup an Excel template that modifies deliverable or activity details:

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_MetricTransfer, SGM_Metadata, SGM_RelatedProjectMetrics, and SGM_Notes worksheets, as none are required for deliverable or activity updates; however, do not delete them.
3. Save the file, add it to the Template Library as a Process Document type, and add the template to a deliverable or activity in a model that uses the template.
4. Open a test project that uses the model and download the template to populate for the deliverable or activity in the SGM_DeliverablesActivities worksheet for the specific deliverable.
5. 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 downloaded data from Accolade.
   • Cells that only upload changed values 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.

6. (Optional) Rename Sheet1 to an appropriate, meaningful name for the display worksheet for users completing the template.
7. Create cell references between the display worksheet (originally Sheet1) and the SGM_DeliverableActivities worksheet. More...

    

   Cell Purpose on Display Worksheet	Instructions
   Cells that only display downloaded data from Accolade	On the display worksheet, in the cells that display the current metric value when the document is downloaded and opened, create cell references to the corresponding "current value" cells on the left side of the SGM_DeliverableActivities worksheet. You can also use field codes to pull metadata and metrics into the document.
   Cells that document owners change to upload back to Accolade	On the SGM_DeliverablesActivities worksheet, in the "new value" cells on the right side of the worksheet, enter cell references to the cells on the display worksheet where the document owner enters information that determines what information is added, updated, or removed for the deliverable and activities to which this template is associated. See step 8 for the columns on the right side of the SGM_DeliverablesActivities worksheet that can update data.

8. In the Action column on the right side of the SGM_DeliverablesActivities worksheet, ensure that a formula or mapped cell enters one of the following values:

• ADD - Adds a new activity to the deliverable identified in the Deliverable ID column. The template can only add activities; it cannot add deliverables. If the Action column is set to Add, the ID entered in the Activity ID column is ignored. You do not need to enter a value for the template to add the activity.
• DELETE - Deletes the activity from the deliverable. The template can only delete activities; it cannot delete deliverables. The document must be set in the process model to allow deletions of versions.
• UPDATE - Updates the deliverable or activity information to match what is in the columns on the right side of the worksheet when the document based on the template is uploaded and published. More...

  To update an existing deliverable or activity, the Deliverable ID and Activity ID must match the original ID in column A and B for the corresponding deliverable or activity. The template can update data contained in the following columns on the right side of the SGM_DeliverablesActivities worksheet:

  • Name
  • OwnerLogin (see notes section below)
  • Start
  • Deadline
  • Function System Name (see notes section below)
  • Status ID (see notes section below)
  • Status Notes

If the Action field is blank or contains any other value, no action is taken on the deliverable or activity identified in that row when the document is saved and published to Accolade.

9. 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.

10. In the display worksheet, remove the lock on the cells that users use to update metric values.
11. Hide the SGM_DeliverablesActivities worksheet, as this is a worksheet 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.

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

Notes:    Valid status IDs are as follows: 1 - Completed. 2 - In Progress 3 - Not Started 5 - Not Required If the Enforce function on user selection option is selected for a deliverable or activity within the process model the project follows, you cannot change the function using this template. The owner in the OwnerLogin column must be assigned the function in the Function System Name column when the enforce option is set. The Workflow in Progress status can only be set through the Accolade website or in Microsoft Project plans. Enter all dates in dd-mmm-yy format. Dates that do not use this format are not updated when the file is uploaded to Accolade. 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.