Updating from Linked Reports

With the Excel data transfer feature, you can export a schedule to Excel, update its activity IDs or assign new resources, and then push those changes back into NetPoint without closing or leaving either application. In this scheme, Excel serves as a seamless data entry and updating method. Other uses for the Excel data transfer feature include spell checking or changing line breaks in activity descriptions, for example.

Fields that can be edited are identified in bold in both the Configure Rows & Columns window as well as in Excel itself. All other columns will be protected, except for the code table, resource table, and target sheets (for more on protected sheets, and for a full list of supported fields, see the notes at the end of this section).

In order execute the Excel data transfer, the file from which you want to update must be generated by an export from NetPoint. For example, to update activity IDs, first export activities and their ID fields to Excel. For more information, see Exporting Linked Reports.

NOTE: NetPoint must remain open at all times during exporting and/or updating. If NetPoint is ever closed, you must close Excel and then open NetPoint again to refresh the link between applications before you can initiate an update. Excel, on the other hand, can be closed and opened as many times as desired without interrupting the connection.

Steps

Editing in Excel

  1. Make sure the Excel file you want to edit and update from was generated by an export from NetPoint. For more information, see Exporting Linked Reports.
  2. If the Excel file you want to edit was just exported from NetPoint and opened, skip to step 4. If not, click File > Import/Export from the NetPoint menu bar.
  3. Choose Open Linked Excel File. NOTE: If you open the desired Excel file from within Windows Explorer or Excel itself, you can still edit and save it; however, to initiate an update, you must close it and open it from the NetPoint menu bar.
  4. Edit any cells whose columns are identified in bold. Once modified, a cell will be colored in blue and its row will be populated with a star under the Changed column.
  5. When finished, click outside of the cell being edited or press Enter.
  6. To update, see Initiating an Update from within Excel.

Initiating an Update from within Excel

  1. If you’re coming from Editing in Excel, skip to step 3. Otherwise, click File > Import/Export from the NetPoint menu bar.
  2. Choose Open Linked Excel FileNOTE: If trying to update from an Excel file that was renamed, moved, or received from another user, first put it back in its project folder and then ensure its filename matches its project folder. If the schedule has been edited since the Excel file was originally exported, changes will be reconciled as follows: If an activity or other object was changed in NetPoint AND changed in Excel, the state from Excel will prevail. If an activity or other object was changed in NetPoint but NOT changed in Excel, the state from NetPoint will prevail. If an activity or other object in Excel is not found in NetPoint, it will NOT be added in NetPoint and it will be removed from Excel. If an activity or other object in NetPoint is not found in Excel, it will be added in Excel. WARNING: Compatibility cannot be guaranteed if a schedule is updated from an unrelated Excel file.
  3. In Excel, choose the ADD-INS tab and click the Update Schedule update icon in the Menu Commands section.
  4. To review the changes first, click Review Changes. To accept without reviewing, click Accept All and skip to step 7.
    1. To deny a change, uncheck the box next to it under the Accept column.
    2. When finished, click the Continue button.
    3. When ready, click the Accept All button.
  5. Excel will open and any changes from NetPoint will be brought in as well.

NOTE: To initiate an update, you must open the file via the NetPoint menu bar, NOT from Windows Explorer or Excel itself.

Adding and Modifying Codes via Excel

  1. First, export the “Default – Code & Code Assignments” template from NetPoint (or any other template as long as it includes the Code Table). For detailed steps, see Modifying/Exporting an Existing Template.
  2. In the linked Excel file, click the Code Table tab.
  3. To add a new code, click in the first empty row under the Name column and enter a description.
    1. Next to the name under the Value column, enter a value. NOTE: Once the name and value have been entered successfully, ADD will appear under the Changed column.
    2. To allow more than one value of the same code to be assigned to the same activity, leave an “X” under the Multi Assignments column.
    3. To enter another value, repeat steps 3-3b using the same description under the Name column for every row.
    4. To enter another code, repeat steps 3-3b using a different description under the Name column.
  4. To delete a value, enter any text (for example, an “x” or “DEL”) under the Delete column.
  5. To delete a code, enter any text under the Delete column for every one of its values.
  6. To modify a code or value, select its description and change it accordingly
  7. When finished, choose the ADD-INS tab and click the Update Schedule update icon in the Menu Commands section.
  8. To review the changes first, click Review Changes .To accept without reviewing, click Accept All and skip to step 9.
    1. To deny a change, uncheck the box next to it under the Accept column.
    2. When finished, click the Continue button.
    3. When ready, click the Accept All button.
  9. Excel will open and any changes from NetPoint will be brought in as well.

Adding and Modifying Resources via Excel

  1. First, export the “Default – Cost, Resources & Assignments” template from NetPoint (or any other template as long as it includes the Resource Table). For detailed steps, see Modifying/Exporting an Existing Template.
  2. In the linked Excel file, click the Resource Table tab.
  3. To add a new resource, click in the first empty row under the Resource Name column and enter a description.
    1. Next to the name under the Cost Per Unit column, enter a value. Once the name and cost per unit have been entered successfully, ADD will appear under the Changed column.
    2. To specify a category, choose from the list.
    3. To enter another resource, repeat steps 3-3b.
  4. To delete a resource, enter any text (for example, an “x” or “DEL”) under the Delete column.
  5. To modify a resource, select one of its cells and change it accordingly.
  6. When finished, choose the ADD-INS tab and click the Update Schedule update icon in the Menu Commands section.
  7. To review the changes first, click Review Changes. To accept without reviewing, click Accept All and skip to step 8.
    1. To deny a change, uncheck the box next to it under the Accept column.
    2. When finished, click the Continue button.
    3. When ready, click the Accept All button.
  8. Excel will open and any changes from NetPoint will be brought in as well.

NOTE: Colors for resources will be set automatically and can modified from within NetPoint. For more info, see Modifying an Existing Resource.

Notes

View Excel Updates Window

Columns can be sorted, added, or removed by left-clicking or right-clicking accordingly.

Screenshot 2015-10-08 14.06.14

Column Definition
Accept Controls whether or not the change will be included.
System ID An internal ID used by NetPoint
Change Type The type of object or item where the change was made.
Field Type The type of field where the change was made.
Old Value The original value before the change.
New Value The new value after the change (if accepted).

Supported Fields

Field Definition
Description The object’s description. The description is for describing the object.
Abbreviated Description The object’s abbreviated description. This abbreviated description is better for displaying on the canvas and is limited to 48 characters.
Estimated Complete The estimated complete field is a user-entered field for estimating the percent of work completed.
ID The object’s ID. The ID is a unique identifier.
Codes Add or remove codes.
Code Names A short description of the code.
Code Values A short description of the value.
Code Assignment Behavior Whether or not multiple values can be assigned to the same activity.
Code Assignments The codes and values assigned to the object. Where an assignment exists, the cell underneath the value’s column will get an “x”
Resources Add or remove resources.
Resource Names A short description of the resource.
Resource Categories A label for describing the type of resource.
Resource Cost The cost of the resource per unit of time.
Resource Assignments (Rate/Day or Rate/Duration)
The quantity of a resource assigned to an activity on a per day or per duration basis.

Tips

Forcing two-line Descriptions

Long descriptions can be forced to two lines in Excel by pressing ALT + Enter.

Unprotecting Excel Sheets

Any column that cannot be edited will be protected in Excel, except for the code table, resource table, and target sheets. When protected, some features in Excel, such as Find and Replace, may not be available. To access these features, unprotect the sheet in the Review tab or menu option using “NetPoint” as the password.

Cut/Copy/Paste

Only cells which can be edited will allow cut, copy, or paste, unless the sheet has been unprotected, as described above. In any case, cut and paste should be strictly avoided, as it will alter the underlying formula of the cells. As an alternative, you can right-click on a cell and choose clear contents.  Copy and paste may be used; however, when pasting, it is recommend to paste only values. This can be accessed via the paste options.

Updating from Earlier Excel Files

The following paragraph discusses what happens if a schedule has been edited in NetPoint and it is updated from an earlier Excel file. If an activity or other object was changed in NetPoint AND changed in Excel, the state from Excel will prevail. If an activity or other object was changed in NetPoint but NOT changed in Excel, the state from NetPoint will prevail. In short, only rows with an asterisk in the Changed column in Excel will have their data pushed into NetPoint. Lastly, if an activity or other object in Excel is not found in NetPoint, it will NOT be added in NetPoint and it will be removed from Excel. If an activity or other object in NetPoint is not found in Excel, it will be added in Excel.