by Ed Merriman
| August 27, 2015
Original Post Date: July 1, 2015
The latest version of PRICE’s “Large Project Engine” allows users to update the values in calculators through Excel. While calculators are designed to be easy to use and user friendly, there are times when it is convenient to update the data in a calculator in Excel. The following blog will provide details on how to do this.
This blog assumes users have installed the “LPE”. The LPE’s installation files are available on the TruePlanning installation media. The LPE is an Excel plug-in which means it will create a menu in Excel which is always available. The LPE can be used with TruePlanning 2014 and the related 2014 service releases. For information and help on installing the LPE and its other features, please contact PRICE.
Users should note that most of the calculators support having their data pulled into and updated from Excel, but there are a handful of calculators that do not support this behavior. Users can use the “List Calculators” button provided by the LPE to get a list of the names of the calculators that can be access through the LPE.
To pull calculator data into Excel perform the following steps:
1. Launch Excel and open a new blank workbook.
2. Set up a sheet to hold the desired calculator data using the following steps:
a. In Cell A1 of the targeted sheet enter the text “Calculator”
b. In Cell B1 of the targeted sheet enter the name of the calculator to be used. Use the names provided by the “List Calculators” button provided by the LPE. Note that each sheet will target a single calculator.
3. Click the “Fill from TruePlanning” button.
4. Verify the desired calculator data has been populated in the targeted sheet.
The format of the sheet with the calculator data is similar to other sheets created by the LPE. The first five columns will be the same as the other sheets and will contain PBS element/structure with the following columns: Order, Level, Cost Object Name, Cost Object Custom Name, and Worksheet Set. After the first five columns, the rest of the columns will be the list of inputs found in the calculator.
Many calculators in TruePlanning provide pulldown lists to help users select from a list of allowed values. These pulldown lists are not available in Excel.
It is quite common for the same calculator to be found in different Cost Objects. The Operating Specification input is used in many Cost Objects and as such if the Operating Specification calculator data is pulled into Excel many of the elements of the PBS will contain data on the sheet containing the Operating Specification calculator data.
The real power of having TruePlanning data in Excel is when the data can be pushed back into TruePlanning. The LPE provides this behavior through the “Update Calculators” button. In order to update calculator data the following requirements must be met:
1. The Excel file must contain one more worksheets properly formatted which includes the following:
a. The value “Calculator” in cell A1
b. A valid calculator name provided in cell B1
c. The first five columns must be: Order, Level, Cost Object Name, Cost Object Custom Name, and Worksheet Set
2. The PBS of the targeted TruePlanning project must match the PBS described in the Excel file.
It is possible to update multiple calculators from the same Excel file. Each calculator’s data will be on its own Excel worksheet.
To push calculator data from Excel into TruePlanning perform the following steps:
1. Open an Excel file that contains at least on sheet with the proper identification of being a calculator sheet for LPE.
2. Click the “Update Calculators” button. This will result in the user being prompted to select a TruePlanning project. Click “OK” to complete the update.
Many TruePlanning calculators, when used in the TruePlanning application, have a ‘dynamic’ quality in that some inputs are only available when other inputs have a specific value. Calculators exposed through Excel will display all possible inputs in order to be complete. This means that users can enter values for calculator inputs in Excel that wouldn’t be possible in the calculator in TruePlanning. Values entered into Excel that are not valid are ignored when those values are pushed into TruePlanning.
Being able to update calculator data in TruePlanning from Excel provide a great deal of power and flexibility. To obtain the latest version of the Large Project Engine or to have questions answered about its use, please contact PRICE System.