Navigation:  OVERHEAD > How to Build a Sheet > User Sheets > Names, References, Controls & Charts

Use of the Name Manager in an Overhead Sheet

 

In any Overhead sheet, cell ranges can be created, but not consumed.

The Paste Names button allows you to paste only global variables. To paste a reference, enter the name in the formula bar.

The Write Variable button is used to export overhead values to global variables. (Refer to Consumption of Variables.)

To refer to entire rows/columns, it is recommended to use syntax such as Sheet!$2:$3 or Sheet!$A:$D. This avoids getting errors if the row/column number of cells exceeds 65,536 / 256 (Excel boundaries).

 

Insertion of Hyperlinks

To create/edit hyperlinks, click Home>Edit>Insert>Insert hyperlinks.

You can enter links to files, web addresses, emails addresses or even ranges in the workbook.

 

Insertion of References to the Minutes View

References can be inserted into a user sheet in the same manner as in a BoQ, but only thru the Formula editor in the context menu. Refer to Bill of Quantities (optional).

Another way is a direct entry thru SQL statement.

 

Therefore two syntaxes are available and et visible in Edit mode (the separators are semicolons):

▪one common to other tabs (refer to Bill of Quantities (optional)

▪SQL (fields wrapped by brackets, the decimal separator is a dot).

Common syntax: GetDataFromMinute(FieldToCheck; ValueInFieldToCheck; FieldToSum; UseWBSQuantities; UseOptions; ShowGrouped)

SQL syntax: GetDataFromMinute("{SQL}";"FieldToCheck=ValueInFieldToCheck; FieldToSum; UseWBSQuantities; UseOptions; ShowGrouped)

 

The SQL syntax can include boolean operators, such as in GetDataFromMinute("{SQL}";"[MATERIAL_KindID]='ELECCABLE' AND [WORKFORCE_KindID=’PLUMB’ AND [Quantity] > 10";"MATERIAL_TotalCost";TRUE;FALSE;FALSE).  The other valid SQL statements are allowed.

CAVEAT The processing is time-consuming. Therefore, never attempt to feed large tables with such formulas. When you need this, consider using the built-in lists if possible or build a specific macro.

 

The result can be put into a global variable as described in Consumption of Variables.

 

Alternative to the Management Database

As an alternative to the management database, meta data can be picked from an external Excel spreadsheet, typically a customers' book with a list (level 1) and possibly a sub-list (level 2).

Proceed as follows:

STRUCTURING THE BOOK UNDER EXCEL

The full example is the file <installation folder>\Samples\Stuff_4_Macros\List Of Companies.xlsx.

 

DEFINING THE FUNCTION

To create a button in a user sheet, refer to Built-in Function Button.

Select Custom functions>Display Excel List and enter the Excel sheet path.

It is recommended to check out the values returned by the function. For this purpose paste the global variables names (second row in the table above) in empty cells.

 

IMPORT (NORMAL MODE)

1.Click the created button; a window pops up that shows the first-level columns

2.Double click a row; if there is no second level underlying, QDV7 creates the global variables as per the Excel sheet and assign them the values in the selected row; the import is finished

3.If  a second level has been defined for the selected row, the sub-table superimposes

4.Double click a row in this sub-table; QDV7 creates the second-level global variables and assign them the values in the selected row.

 

An example shows in the Multi-languages sample>Overhead workbook>Sample Function.

The alternative summarizes as follows:

Controls and Charts

To include controls and histograms in the user sheets Sheet of Sales and Cash-flow, right-click and select Insert control.

For details, refer to How to Ease Inputs with Controls.

In Edit Mode, controls and charts can be created in Excel™ (menus Developer and Insertion respectively) for any sheet.

 

Goal Seek

To find the cell value necessary to reach a target value in a cell referring the first cell thru a formula, select Home>Compute>Goal Seek. It works as in Excel™.