Navigation:  Other Data Sources Attached to an Estimate > In-Row Workbooks

Use Cases

An in-row Workbook is a worksheet attached to

▪a row in a section of minutes ("in-row" proper) including - but with restrictions - the Overhead Minute

▪the header of a section of minutes ("in-minutes" or cross-minutes)

▪an article in a database ("in-row" proper) including - but with restrictions - a database of the estimate

▪a task/branch/total in the expanded WBS ("in-WBS").

 

It provides the row with calculation capability and, for minutes and articles, writing capability.

 

 

Writing to a cell is equivalent to creating in this cell a formula; its result shows in dark blue and cannot be edited directly (exemplified by Y).

 

For a set in a database, refer to How to Empower a Set with Calculation Capabilities.

Using the Column Manager (type: other file, refer to How to Create Free Columns with Custom Behavior) and inserting the related column into the Minutes view, more workbooks can be associated to any Minute. No direct editing is allowed: save and reimport the file.

 

Procedure

Tool: bar Data, group Microsoft Excel

 

To link a minute/article/task/branch/total to its workbook (for header, see further):

1.Select a row for which the item Create in-... Workbook is enabled (i.e. the row (or the header) has not yet an attached workbook)

• to create an in-minutes workbook, select any row in the section

•in a database, switch to Edit mode first

2.Click Create an in-... Workbook (alternative: click the drop-down button; the context determines the nature of workbook; for Minutes, an In-row workbook has precedence over an In-minutes workbook); a worksheet embeds into the table letting the left column visible

3.For any cell, define the value directly or thru a formula (if the cell is locked, click Home>Format>Lock cell), or set a link using the context menu:

 

✓ Define as input from global variables. (To create/edit a global variable, refer to Global Variables.)

✓ Define as input from row, i.e. from the minute/article/task/branch/total; as a result the value shows and the In-row workbook cell turns light yellow

     

 

NOTE The In-row workbook is recomputed (by clicking Compute Costs) only when a used input is changed

 

✓ Define as output to row: the In-row workbook cell will autofill the selected field's value in the minute/article (the value shows dark blue; double-click it to access the workbook); the In-row workbook cell turns green

 

QDV7 applies this value to ALL the Minutes that have no value in the specified column. You can still overwrite them to 0 if needed.

 

✓ Define as input from overhead (To create a range of cells, refer to Range of Cells.):

❖ select the cell which will be the range starting point (make sure enough space is free)

❖ right-click Define as input from overhead

❖ select the relevant entry

❖ click OK; this pastes the range of cells, whose background turns light yellow

Whenever the Overhead workbook is modified, the In-row workbook must be recomputed. If the source results from a calculation, to force the calculation, check the box File>Options>Calculations>Always calculate in-row and minute workbooks ...

 

✓ Undefine as input field: the minute/article/task/branch/total does not feed anymore to the selected cell(s); the names are stripped away from the manager, the yellow cells turn white, but the value is kept

✓ Undefine as output field: the selected cell(s) do(es) not feed anymore to the minute/article; the names are stripped away from the manager, the green cells turn white

 

4.Right-click and select (Save and) close (regardless of the mode); the workbook closes and the returned values show in dark blue; a flag (x) shows in the column Microsoft Excel workbook

5.To edit the workbook, select the row and click Edit Workbook (or double-click a returned value)

If you click the drop-down icon Create Workbook, depending on the context an In-row workbook, In-minutes workbook or In-WBS workbook is created.
In-row workbook has priority over In-Minutes workbook.

SHORTCUT in place of step #2:

▪for Minutes, in Edit mode right-click and select Insert columns>/Microsoft Excel workbook

▪for Articles, in Edit mode click Administration>Columns>Insert special column>Workbook

▪for expanded WBS, right-click and select Insert special column>Workbook

then double-click the intersecting cell (step 7).

 

NOTES

- In the case of a database, "Define as input from global variables" and ''Define as input from overhead" present the variables/names of the current estimate where applicable; the input takes the value of the variable in the estimate where the article is inserted

- When you select View>Show>Check and Repaint, QDV7 recomputes all in-row workbooks

- If the user has no permission to editing a workbook (refer to User Profiles), the workbook opens in read-only mode

-To find, and in Edit mode, replace a data, click Home>Search

 

Cell Format

RESTRICTIONS

Example

To use a sliding scale of charges, insert a formula with conditions on quantity (factor):

 

 

Cell Format

 

Cross-Minutes Workbook

A workbook can handle cells from various rows in one section of Minutes. Such a workbook is located in the section header. These rows must be identified in an absolute way.

Procedure (Edit mode):

1.Insert the column based on the native field ID for workbook

2.Enter any string of chars in this column at the intersection with the rows of concern (choose a mnemonic)

3.When you define a cell as input/output, select the field AND the row in the text area "Select ID of the row" by its ID; in the combo and in the Name manager, the mnemonic is followed by .<Id for workbook>

 

Cross-minutes addition: if several rows receive the same string in step #2, and, in the workbook, a cell receives an input from a numeric column of rows identified by this string, the cell takes the sum of the column values across the rows (to view the sum, save, close and reopen the workbook).

 

OPTION LOCKING

Click File>Options>Editing>Adapt minutes only via minutes workbook.

As a result, outside the Edit mode, the sections of minutes where in-minutes workbooks are defined are grayed out; you can still modify the headers and view the in-minutes workbooks. This is helpful if an administrator has denied edition (refer to Actions). Direct access from the Expanded WBS: double click the colored cell (Cost per unit generally) or press F4; to return, press again.

 

Extra Functions

To insert GUI Widgets, select in the step 5 Insert control.

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™. Applicable to minutes and tasks.

 

Using the Name Manager

The Name Manager is described in Managed Data.

To feed to the Name Manager, refer to Global Variables and Range of Cells.

To enroll a cell directly into the Name Manager, select Define name and name the cell (alternative in the context menu: Define name). There is no use of naming a range of several cells as it can not be pasted.

The defined input and output data show in the Name Manager (prefixes IN and RET); to repeat these data in a cell, select Paste name.

The following figure shows how the Name Manager builds up and can be used: