Spreadsheets in Expert Estimation

Expert Estimation allows the creation and syncing of Global/Managed Defines, as well as linking named cells in Spreadsheets with the Base and Sell Rates of Base Resources. This allows changes made to a spreadsheet to update values within the project.

Once a Spreadsheet has been added to the project, a local copy is stored in the database and cannot be edited directly. To make changes to the values, you must first save a local copy of the spreadsheet through the use of the Save Spreadsheets button, then re-upload the updated spreadsheet to the project via the Add/Replace Spreadsheet button.

Note: After an updated spreadsheet is re-uploaded, you are able to sync specific usages of the named cell(s) by right-clicking in the Properties pane and pressing the Sync button. If the values are out of sync the Requires Sync column will be checked.

GLOBAL DEFINE LINKING

When a spreadsheet has been uploaded to a project, any Named Cells within the spreadsheet can be synced as Global Defines for the project. Expanding the tree view for each spreadsheet will list the names of the Named Cells from the spreadsheet and their associated values.

A Global Define will be added for each Named Cell selected and will be synced to the value in the spreadsheet when using the Create Linked Global Define button.

When using the Create Linked Global Define button, if there are existing Global Defines in the project, a dialogue window will be displayed asking if you wish to reuse the existing defines or create new defines for the selected Named Cells.

When an existing spreadsheet is overwritten by an updated file, any linked Global Define values will not be automatically synced. The realignment of these values is done from the Global Defines window, using the Sync Item function.

If a spreadsheet is removed, any links to Global Defines will be broken. However, the defines will retain their value if this occurs. The spreadsheet link can be re-established by adding and re-syncing the spreadsheet’s Named Cells.

The property “Import Numeric Named Cells Only” allows you to filter the list of Named Cells that are made available to be synchronised with Global Defines. When enabled, only Named Cells that have a value, and are able to be converted to numbers are identified and made available. When disabled (which is the default) all Named Cells will be displayed. If those Named Cells aren’t able to be converted to a numeric value, they will be imported and synchronised with a value of zero.

RESOURCE RATE LINKING

The Named Cells within a spreadsheet can also be linked to the Base/Sell Rate of resources. This is done by selecting the Base Resources to link and clicking the Link function from the Resource Explorer – Ribbon Menu.

When linked, the name of the Spreadsheet, Named Cell and Named Cell Value will be displayed alongside the resource in the Base Rate Spreadsheet Named Reference and Base Rate Spreadsheet Value or the Sell Rate Spreadsheet Named Reference and Sell Rate Spreadsheet Value columns. An Excel symbol will also appear next to the Base/Sell Rate of the resource.

When an existing spreadsheet is overwritten by an updated file, any linked Rate values will not be automatically synced. The refreshing of the values is done from the Resource Explorer window using the Sync Values function.

If a spreadsheet is removed, any links to a resource’s rate will be broken. However, the resource will retain their value if this occurs. The spreadsheet link can be re-established by adding and re-linking the spreadsheet’s Named Cells.

SUBSTITUTING SPREADSHEETS / NAMED CELLS

Within the Spreadsheets window, there are various ways that you can substitute Spreadsheet(s)/Named Cell(s) for another Spreadsheet/Named Cell each with different scope and impact on the project.

• When using the Substitute Spreadsheet/Named cell button from the right-click context menu (with a Named Cell selected) a dialogue window will be displayed allowing you to substitute all currently selected Named Cells with a Named Cell from the same spreadsheet, or from a different Spreadsheet completely.
• Selecting any Spreadsheet and pressing the Substitute Spreadsheet button from the ribbon menu or via the right-click context menu will display a dialogue window allowing you to substitute all references to the selected spreadsheet with a different spreadsheet.
• From the Properties pane, you can select individual usages of a Named Cell(s) to be substituted to another spreadsheet via the Substitute Spreadsheet button in the right-click context menu.

Note: When substituting whole Spreadsheets, any Named Cells that are linked will break if the Named Cell is not present in the new Spreadsheet. A Project Message will be raised for each of these instances so that you can review them individually.