Direct Ref

Location on ribbon: Tab DVD Addin → Group Formula → Menu Calculate Menu

Command ID: btnAssignDirectReference

License required

This command needs an active DVDAddin account (Standard or PRO). It is fully available during the trial period.

What it does

This command links two tables with direct reference formulas built on a shared key column. The add-in matches the source keys against the target keys and writes, into the last column of the source range, a formula pointing straight at the matching value cell in the target table.

Unlike a VLOOKUP that scans a whole table, the generated formula addresses exactly one cell — lighter to recalculate, easier to audit, and obvious at a glance where the number came from. Ideal for pulling unit rates from a rate sheet into a BoQ, or approved quantities into an interim payment sheet.

How to use

1. Prepare the source range: its first column is the lookup key, its last column is where the reference formula will be written.
2. Prepare the target range with at least 2 columns: first column = key, last column = the value to reference.
3. Go to tab DVD Addin → group Formula → menu Calculate Menu → click Direct Ref.
4. The tool window opens on the Direct Ref tab (the Calculate tab group).
5. Pick the ranges for Source and Target (the target may live on another sheet).
6. Click Assign. Reference formulas are written into the last column of the source range on every row whose key matched.

Example

Sheet RATES (target) has item codes in column A and unit rates in column D. Sheet BOQ (source) has item codes in column B and an empty column G waiting for the rate.

• Source = B10:G250 on sheet BOQ
• Target = 'RATES'!A5:D400

Click Assign: the row whose code AF.11220 sits in B37 gets a formula in G37 pointing directly at that code's rate cell on RATES. When the client later revises pricing, editing the rate sheet updates the whole BoQ.

Notes

• The target range must have two or more columns; with a single column the tool cannot tell key from value.
• The layout convention is fixed: the first column of each range is always the key, the last column is where the value is written or read. If your table is arranged differently, select the range so those two edge columns are correct.
• Existing content in the last column of the source range is overwritten.
• Rows whose key is not found in the target get no formula — check for typos and stray spaces in the codes before running.
• The formulas point at specific cells: inserting or deleting rows in the target keeps them aligned, but re-sorting the target table can make a reference point at the wrong key.

Related

• Lookup Trace — verify the other way round which cell a lookup formula reads from.
• Autosum — sibling tab that builds SUBTOTAL rows once the rates are in place.
• Lock Ref — add $ signs to pin a lookup range before copying formulas around.