QS Tools (Quantity Surveying)

Specialized commands for QS (Quantity Surveyor / cost estimators) — show formulas, trace VLOOKUP chains, check cell dependencies.

3 commands in this group

Command	Purpose	License
Show Formulas	Toggle View → all formulas display as text instead of results	Free
Trace Lookup	Detect and visualize VLOOKUP/INDEX/MATCH chains	Premium
Cell Dependencies	Visualize precedents / dependents of the current cell	Free

Show Formulas

DVD Cons → group QS → click Show Formulas (fx).

Toggle between 2 modes:

• Normal: cell shows result (e.g. 1,234,567).
• Show Formulas: cell shows source formula (e.g. =B2*C2*1.1).

Equivalent to: Excel Formulas tab → Show Formulas (Ctrl+`).

Differences:

• DVD Cons version offers an option to apply only to selected range (Excel built-in applies to whole sheet).
• Can highlight formulas with cross-sheet dependencies (yellow background) — quickly find cross-sheet references.

Use cases

• Audit colleague/client BoQ files — see if there are hidden formulas.
• Debug bad formulas in complex estimation tables.
• Print the formulas table to explain the calculation logic.

Trace Lookup

DVD Cons → group QS → click Trace Lookup.

Detect and draw chains of cells with VLOOKUP / INDEX-MATCH / XLOOKUP — reference graph showing:

• Cell A2 = VLOOKUP(B2, Catalog!A:C, 3, FALSE) → addin highlights A2 + the source range on Catalog sheet.
• Cell A3 = =A2 * 1.1 → addin links A3 ← A2.
• Cell B2 = =INDEX(PriceList, ...) → continues tracing.

Output

1. Visual arrows in Excel (like built-in Trace Precedents) but:
   • Cross-sheet tracking (built-in only tracks within same sheet).
   • Cross-workbook tracking (built-in doesn't support).
2. Sidebar dialog listing cells in the chain:

   [Cell] → [Function] → [Reference cell]
   A2     VLOOKUP       Catalog!A2:C100
   A3     Multiply      A2
   B5     INDEX-MATCH   PriceList!A:Z

3. Highlight color by lookup type:
   • VLOOKUP → red.
   • INDEX-MATCH → blue.
   • XLOOKUP → purple.

Use case

• Bid file B has thousands of VLOOKUP formulas referencing Catalog, UnitPrice, Norms sheets. Before editing, you need to know which cells use UnitPrice to avoid breaking → Trace Lookup → quick highlight.

Premium

Trace Lookup requires an active license.

Cell Dependencies

DVD Cons → group QS → click Cell Dependencies.

Opens a dialog showing:

• Precedents — cells the current cell POINTS TO (input).
• Dependents — cells that DEPEND on the current cell (consumers).

Example cell A1 = =B2 + C3, cell D5 = =A1*2:

• A1 Precedents = {B2, C3}.
• A1 Dependents = {D5}.

Click cell in dialog → jump to it.

Equivalent to Excel Trace Precedents + Trace Dependents (Ctrl+[ / Ctrl+]).

Differences:

• Multi-level — not just direct precedent (1 level), but tracks all the way to root (n levels).
• Cross-sheet + cross-workbook.
• Dialog list view instead of arrows (easier to navigate long chains).

Hierarchy expand

Click ▼ before each cell in the list → expand the next level:

▼ A1 (current cell)
   ▼ B2 (= D5 + E5)
      • D5 (raw value)
      • E5 (raw value)
   ▼ C3 (= F1 * G1)
      • F1 (raw value)
      • G1 (= H1) ← cyclic? click to check
        • H1 (raw value)

→ Visualize the entire dependency tree.

Detect circular reference

If the chain has a cycle (A → B → A), the dialog shows ⚠️ Cycle detected and marks the cells in the cycle with red background.

License gating

Command	Free	Activated/Trial
Show Formulas	✓	✓
Cell Dependencies	✓	✓
Trace Lookup	✗	✓

Related

• Formula Tools — Autosum, Round, Lock Reference (same category).
• Error Check — scan #REF! / #NAME? / #DIV/0! across the workbook.
• Connect Server — sync BoQ data across multiple machines.