Consumption of Variables

Navigation:  Other Data Sources Attached to an Estimate > Variables >

Consumption of Variables

Previous pageReturn to chapter overviewNext page

Direct Consumption

Global variables can be invoked in:

the Expanded WBS's free columns (manual entry)

the Overhead workbook

the Fields Manager

a workbook meant for Minutes and databases (In-Row Workbooks)

technical specifications (Publishing)

database sets

report headers.

To consume a Global variable in a WBS's free column, type a formula in any column's cell.

To consume a Global variable in the Fields Manager, type the variable name in the editor of formula.

To consume a Global variable in a free sheet formula in Edit mode or in report headers:

1.Select a cell

2.Click Paste Name (main or context menus)

3.Select the variable.

As a result, the current value shows, and the variable is enrolled in the combo box in the sheet left upper corner and listed out in the Name Manager. (Refer to Managed Data.)

APPLICABILITY

Location

Pasteable Names

Minute workbook or WBS workbook

GLV, SYS. Thru Define as input from the Overhead: Overhead ranges

Overhead workbook

GLV, SYS. Thru formula: local cell content (directly or thru name) and protected names

Report of WBS, Minute or Nomenclatures type

GLV, SYS*, local ranges

Report of Overhead type - QDV7 Sheet

None (locked cells)

Report of Overhead type - Sheet added in Excel

GLV, SYS, cell name

* If QDV7 shows #NAME?, add first the variable to the Name Manager.

 

To delete a pasting in the WBS, the report or a free sheet in the Overhead workbook, select the row and click Delete; the cell value becomes #NAME?.

To paste the global variable value from another statement or version (refer to How to Manage the Versions), enter a formula:

- Applicability: WBS's free column, overhead workbook, overhead report, report header

- The syntax requires to wrap the parameters with double quotes:

for a progress statement use a positive value or just "Estimate"; formula: =GetVariableFromProgress("<Global variable name>";"<Progress statement>")

for a version, formula: =GetVariableFromVersion("<Global variable name>";"<Version>")

- Quick input using a drop-down list or text area:

paste name

To check the formula, double-click the value in the estimate's column. If for Progress statement, you get #N/A, replace the negative value with a positive one.

The Global variables and References to Overhead ranges can also be merged in a Word™ document meant for the customer. Refer to Using Variables in Word.

 

Linking a Variable to a Cell

Empty cells in a user sheet can be used to

transform the content of a cell into the content of a global variable, e.g., for Mail Merge in Word (refer to Using Variables in Word)

edit a variable with a formula.

 

Procedure:

1.Put a formula into an empty cell (alternative: do this at the end)

2.Edit a new global variable without value; this creates variables GLV_ and GLVOUT_

3.Select the cell, click Write Variable and select the variable GLVOUT_; as a result, the variable GLV_ takes the value calculated with the formula.

 

More on this Exchanges with the Estimating Module

More on this Exceptions above (SYSOUT_)

Examples:

- without formula: see the Multi-languages sample estimate>Overhead workbook>Sheet of Sales>bottom

- with formula:

In an Overhead free sheet or in the Expanded WBS, put in an empty cell (e.g., C16) the formula =NUM2WORDS(C10,"<language code>") where C10 contains the formula =SYS_TotalSellingPrice. For the language, refer to Multilingualism.

Create the variable GLV_Amount_In_Words (with texte type) and assign GLVOUT_Amount_In_Words to C16. To show the general amount in words, paste GLV_Amount_In_Words anywhere.

Syntax for the general amount in words (wrap arguments in double quotes):

Num2Words(cell reference) returns the value in the current language; the separator between units and tenth is rendered with "point" in English, "virgule" in French, "komma" in Dutch ...

Num2Words(cell reference;language code) returns the value in the language code if it exists (the language is hard-coded, but SYS_Lang_UI is allowed as well, not SYS_Lang_Report)  

Num2Words(cell reference;language code;Currency singular;Currency plural;Singular of hundredth;Plural of hundredth) adds currency units and hundredth of unit in words (the separator between units and tenth is rendered with "and" in English, "et" in French, ... and a period is automatically appended)

Num2Words(cell reference;language code;Currency singular;Currency plural;Singular of hundredth;Plural of hundredth;Gender of currency;Gender of hundredth) adds the gender of currency unit and hundredth when the language requests so (allowed values are M for Masculine, F for Feminine, N for Neutral)

Num2Words(cell reference;language code;Currency singular;Currency plural;Singular of hundredth;Plural of hundredth;Gender of currency;Gender of hundredth;Appended text) appends a supplemental text, e.g., " excluding VAT."

 

A example shows in the sample Multi-languages_Sample_with_Documents/Overhead/Sheet of Sales.

 

NOTES

- Add a space before any Appended text

- If a variable is already written., any new writing will delete the preceding link

- The general amount can also show in words only after the publishing step. Refer to How to Build a Master Document (Template). There the arguments are separated with commas and does not need double quotes.