Quick Input

Site diaries, material logs and inspection registers all share one property: the same work item, crew name or material has to be typed hundreds of times, and a single stray space is enough to split one PivotTable row into two.

The Quick Input commands (tab DVD Addin → group Text and Number) fix that with a suggestion popup that opens next to the cell you are filling: type a few characters, pick a row, press Enter — the value lands spelled exactly like the rows above it and the cursor drops to the next row.

Commands in this group

Command	What it does
Quick Input	Opens the suggestion popup next to the active cell (shortcut Ctrl+Alt+I), fed by the cell's Data Validation list or the unique values in the column above
Auto-suggest on cell select	Toggles whether the popup opens by itself every time you select a cell
Configure source ranges…	Defines Target → Source rules, including sources on another sheet, and fills several columns from one pick

The three are three levels of the same feature: use it with no setup at all (Quick Input), automate the trigger (Auto-suggest), and point it at the proper master table instead of just the column above (Configure source ranges).

Entering a table from a master list

1. Use it with no setup — put the cursor in a cell below a column that already has data and press Ctrl+Alt+I (or click Quick Input). The popup shows a source hint line, a search box, the result list and a status line; type to filter, arrow keys to highlight, Enter to insert, Esc to dismiss.
2. Point it at the master table — when the source lives on another sheet, click Configure source ranges… → New rule and fill in Source range and Target range (each has a Pick… button for selecting directly on the sheet).
3. Declare the header and the search column — tick Source has header row if the first row of the source is a header, then choose the Trigger column, the source column the popup searches on.
4. Map several columns — under Advanced — per-column mappings, click Add mapping to add Target range ↔ Source column pairs. An empty list means a positional fill.
5. Save — click Save rule (Close does not save a rule you are still editing), then Close.
6. Turn on auto-suggest for a bulk entry session — click Auto-suggest on cell select; the Excel status bar reports Quick Input: ON — popup auto-opens on cell select. Click it again afterwards so the popup stops jumping out while you write formulas.

Working the popup:

Key	Effect
Ctrl+Alt+I	Open the suggestion popup for the active cell
Any character	Filter the list by what you typed
↑ ↓	Move the highlight through the list
Enter (or double-click)	Insert the value and move the cursor one row down
Esc	Dismiss the popup, leaving the cell unchanged

Example

Sheet Materials is the project's master catalogue; sheet BoQ is where the data is entered.

Source column	Content	Matching target column
A	Material code (XM.PC40)	C10:C200
B	Material name (PC40 cement)	B10:B200 — the main target range
C	Unit (t)	D10:D200
D	Unit rate (1,780,000)	E10:E200

One rule covers it: Source range = Materials!A1:D500, Target range = BoQ!B10:B200, Source has header row ticked, Trigger column = Material name, plus three mappings for columns C, D and E.

While filling the BoQ, put the cursor in B15 and type cement — the popup shows the catalogue as a table with all source columns; pick the right row, press Enter, and the name, code, unit and rate are all filled in one go.

Tips and caveats

• With no rule defined, the list is built from the unique values in the same column above the active cell — at the top of a table, with nothing above, there is nothing to suggest.
• Rules are stored by range address: if you later insert or delete rows and columns so the target shifts, reopen the dialog and update the addresses.
• Give the source table a header row and tick Source has header row, otherwise the header itself appears as a suggestion.
• When the target columns are in a different order than the source columns, mappings are mandatory; leaving the list empty is only correct for a positional fill.
• Rules are saved inside the .xlsx itself, so they travel with the workbook when you send it to a colleague; the Auto-suggest on/off state lives in the Windows registry instead, so it belongs to the machine and survives restarting Excel.
• On very large sheets, scanning the column for unique values on every selection feels slightly slower — switch auto-suggest off while browsing or formatting big tables. Quick Input and Configure source ranges… need an active DVDAddin account (the Trial covers both).

See also

• Smart Data Cleaning — tidy the older hand-typed rows before switching to catalogue-driven entry.
• Bulk Find / Replace — bring legacy spelling variants back in line with the master list.
• Numbering — number the rows once the table is filled.