Lock Ref

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

Command ID: btnLockCellReferences

What it does

This command converts the reference style of every formula in the selected range between four forms: A1, $A1, A$1 and $A$1. Instead of pressing F4 cell by cell, you convert a whole column or table with one Apply.

It matters most right after you build a master formula and are about to fill it down hundreds of rows: lookup tables, coefficient cells and base rates must be pinned while the per-row data cells must stay relative. In the other direction, stripping all $ signs is what lets a table shift correctly when you move it into a new file.

How to use

1. Select the range of cells whose formula references you want to change.
2. Go to tab DVD Addin → group Formula → menu Calculate Menu → click Lock Ref.
3. The Add/remove cell lock window opens with a Lock mode block of four choices:
   • No lock — A1
   • Lock row — $A1
   • Lock column — A$1
   • Lock both row and column — $A$1 (default)
4. Underneath is the Skip hidden cells checkbox (on by default).
5. Pick the mode and click Apply — the formulas in the selection are rewritten in place.
6. The window stays open: select another range and click Apply again, or click Close when done.

Example

A rebar take-off sheet where G8 looks up unit weight by bar diameter:

Before	After (Lock both row and column)
=E8*F8*VLOOKUP(D8,BARTABLE!A2:B12,2,0)	=E8*F8*VLOOKUP(D8,BARTABLE!$A$2:$B$12,2,0)

If you only want the row pinned so the formula can still be dragged sideways across floor columns, choose Lock row — $A1 instead of locking both directions.

Notes

• The chosen mode is applied to every reference in the formulas of the selection, including references that should have stayed relative. Keep the selection tight around what you actually want changed.
• Only cells containing formulas are affected; typed numbers and text are untouched.
• Skip hidden cells leaves hidden or filtered-out rows and columns as they are.
• The window is modeless, so the selection can be changed between Apply clicks.
• Formulas are overwritten and the change cannot be undone with Ctrl + Z; save the file before running it on a large sheet.

Related

• Rounding Func — the same style of window, adding or removing ROUND around formulas.
• Direct Ref — build cross-table reference formulas before pinning the lookup range.
• Error Check — toggle formula error flags while reviewing the range you just edited.