Skip to content
EXAMPLE

Controlled Trade Entry Form

Summary

Build a trade-entry row that validates required fields, assigns an ID and time, appends completed trades, clears the form, and maintains dropdown lists.

Files Used in This Example

Prerequisites

  • A defined set of required trade fields and controlled lists.
  • A reserved history range for completed entries.

Step 1: Gate completion on required fields

Create the unique ID only after all required fields are populated; derive the timestamp from the completed state.

=IF(COUNTBLANK(B6:F6)>0,"",U.UUID())

=IF(H6="","",U.NOW())

Step 2: Seed values without overwriting edits

Paste a seed price only when the symbol is present and the user has not already entered a price.

=IF(OR(B6="",D6<>""),"seed price",U.PASTE(IF(D6<>"",D6,IFERROR(VLOOKUP(B6,Q6:R30,2,FALSE),"")),D6))

Step 3: Append the completed row and clear the form

When the new ID differs from the prior row, append the entry; once it matches, clear only the input cells.

=IF(H6="","record trade",IF(H6=H7,U.CLEAR(B6:F6),U.PASTE(U.REGION(B6:H6),B7:H51)))

Step 4: Maintain unique dropdown values

Normalize and append a new list entry, remove duplicates, and paste the controlled list back into its range.

=U.PASTE(U.VUNIQUE(U.VAPPEND(K8:K20,UPPER(K5))),K8:K20,,,,TRUE)

Result

Only complete entries receive an ID and time, completed rows move into history, and the entry row resets without overwriting deliberate user edits.

What’s Happening

What’s happening here?

The ID is both the validation outcome and the append/clear state marker. Keeping seeding, completion, history, and list maintenance in separate formulas makes the workflow inspectable.