Order intake: data validation

About this example

An order intake form on the Svelte 5 spreadsheet shell with Excel's Data Validation keeping bad entries out at the keyboard: Region and Product are in-cell dropdown lists, Product read from a range on a second sheet; Qty is a whole number from 1 to 500 with a Stop alert whose Retry reopens the cell; Discount is a decimal up to 0.2 with a Warning that asks whether to keep the entry; Ship by is a date on or after the order date, a relative bound (=A2) that moves with the row as in Excel. Unit price is an XLOOKUP into the price list and the total follows. Enterprise, in @svgrid/enterprise.

The order form a sales desk fills in all day, with Excel's Data Validation keeping bad entries out at the keyboard:

Region, Product lists. The arrow on the cell (or Alt+Down) drops the choices; Product reads its list from the Lists sheet, so adding a product there adds it here. Qty a whole number from 1 to 500, Stop style: 900 is refused with the message, Retry reopens the cell. Discount a decimal up to 20%, Warning style: 35% asks whether to keep it, Yes lets it through. Ship by a date on or after the order date in column A: the bound is =A2, a relative formula that moves with the row, as it does in Excel. Unit price, Total formulas: the price is an XLOOKUP into the Lists sheet, the total follows Qty, price and discount.

Validation checks what is TYPED, as Excel's does: a paste lands as it is. Data > Data Validation opens on the rule at the active cell, and Clear All takes a rule off a selection.

Try: type 900 into a Qty cell, then 0.35 into a Discount cell, then a date before the order date into Ship by. Pick a product from the arrow on a blank row and watch the price and total fill in.

Imports, features and API used

Imports: @svgrid/enterprise

Frequently asked questions

What does a Stop alert do that a Warning does not?

Stop refuses the entry: the cell keeps its old value and Retry reopens it with what was typed. Warning asks whether to keep the entry anyway; Yes writes it, No reopens the cell, Cancel leaves it as it was.

Can a list come from another sheet?

Yes. The Product rule's source is =Lists!$A$2:$A$7, so adding a product to the Lists sheet adds it to the dropdown. A comma list (Yes,No) or a defined name work the same way.

Does the rule move with the row?

A relative bound does. Ship by is "on or after =A2" written for the first row; on row 5 it reads A5, the way Excel moves a relative reference in a rule. An absolute $A$2 would stay put.

Is a paste validated?

No, as in Excel: validation checks what is typed. A paste lands as it is, and so does a fill.

Related documentation

• The spreadsheet shell

Related articles

• Row Drag-and-Drop Reordering in a Svelte Data Grid - Wire up drag-to-reorder rows in SvGrid using a handle cell, native HTML5 drag events, and a reactive state array - no library required.
• Building an Order Management Dashboard in Svelte - How to wire up an order management grid with master-detail line items, status workflows, server-side data, and bulk fulfillment actions using SvGrid.
• A Fill Handle (Drag to Fill) in SvGrid - Build a working spreadsheet-style fill handle on top of SvGrid's cell selection and editing - pointer tracking, range highlighting, series fill, and undo/redo integration all covered.

Source code (461-order-intake-validation.svelte)

<script lang="ts">
  /**
   * 461. Order intake: data validation
   * ----------------------------------
   * The order form a sales desk fills in all day, with Excel's Data
   * Validation keeping bad entries out at the keyboard:
   *
   *   Region, Product   lists. The arrow on the cell (or Alt+Down) drops
   *                     the choices; Product reads its list from the Lists
   *                     sheet, so adding a product there adds it here.
   *   Qty               a whole number from 1 to 500, Stop style: 900 is
   *                     refused with the message, Retry reopens the cell.
   *   Discount          a decimal up to 20%, Warning style: 35% asks
   *                     whether to keep it, Yes lets it through.
   *   Ship by           a date on or after the order date in column A: the
   *                     bound is =A2, a relative formula that moves with
   *                     the row, as it does in Excel.
   *   Unit price, Total formulas: the price is an XLOOKUP into the Lists
   *                     sheet, the total follows Qty, price and discount.
   *
   * Validation checks what is TYPED, as Excel's does: a paste lands as it
   * is. Data > Data Validation opens on the rule at the active cell, and
   * Clear All takes a rule off a selection.
   *
   * Try: type 900 into a Qty cell, then 0.35 into a Discount cell, then a
   * date before the order date into Ship by. Pick a product from the arrow
   * on a blank row and watch the price and total fill in.
   */
  import { SvSheet, createWorkbook, createSheetDocument, type CellFormatEntry } from '@svgrid/enterprise'

  const PRODUCTS: ReadonlyArray<readonly [string, number]> = [
    ['Standard seat', 49], ['Pro seat', 89], ['Enterprise seat', 149],
    ['Onboarding', 1200], ['Training day', 850], ['Premium support', 300],
  ]
  const REGIONS = ['North', 'South', 'East', 'West']

  const ORDERS: ReadonlyArray<readonly [string, string, string, number, number, string]> = [
    ['2026-09-01', 'North', 'Pro seat',        12, 0,    '2026-09-05'],
    ['2026-09-01', 'East',  'Onboarding',       1, 0,    '2026-09-15'],
    ['2026-09-02', 'South', 'Standard seat',   40, 0.1,  '2026-09-04'],
    ['2026-09-03', 'West',  'Enterprise seat',  8, 0.05, '2026-09-10'],
    ['2026-09-04', 'North', 'Premium support',  3, 0,    '2026-09-04'],
    ['2026-09-04', 'East',  'Training day',     2, 0,    '2026-09-22'],
    ['2026-09-05', 'South', 'Pro seat',        25, 0.15, '2026-09-09'],
    ['2026-09-08', 'West',  'Standard seat',   15, 0,    '2026-09-12'],
  ]
  const ROWS = 16                        // eight filled, eight open for entry
  const LAST = ROWS + 1

  const orders = [
    ['Order date', 'Region', 'Product', 'Qty', 'Unit price', 'Discount', 'Ship by', 'Total'],
    ...Array.from({ length: ROWS }, (_, i) => {
      const r = i + 2
      const o = ORDERS[i]
      const typed = o ? [o[0], o[1], o[2], String(o[3]), '', String(o[4]), o[5], ''] : ['', '', '', '', '', '', '', '']
      typed[4] = `=IF(C${r}="","",XLOOKUP(C${r},Lists!A2:A7,Lists!B2:B7))`
      typed[7] = `=IF(OR(C${r}="",D${r}=""),"",D${r}*E${r}*(1-F${r}))`
      return typed
    }),
    [],
    ['Orders', `=COUNTA(C2:C${LAST})`, '', `=SUM(D2:D${LAST})`, '', '', 'Total', `=SUM(H2:H${LAST})`],
  ]
  const lists = [
    ['Product', 'Unit price', '', 'Region'],
    ...PRODUCTS.map(([name, price], i) => [name, String(price), '', REGIONS[i] ?? '']),
  ]

  const wb = createWorkbook([
    { name: 'Orders', cells: orders },
    { name: 'Lists', cells: lists },
  ])

  // The rules live in the document, as they would in a saved workbook.
  const doc = createSheetDocument({ workbook: wb })
  const body = (col: number) => [[1, col, ROWS, col]] as const
  doc.get('Orders').validation = [
    { id: 'region', rects: body(1), allow: 'list', value1: '=Lists!$D$2:$D$5', ignoreBlank: true, inCellDropdown: true,
      alert: { style: 'stop', title: 'Region', message: 'Pick one of the four regions.' } },
    { id: 'product', rects: body(2), allow: 'list', value1: '=Lists!$A$2:$A$7', ignoreBlank: true, inCellDropdown: true,
      alert: { style: 'stop', title: 'Product', message: 'Pick a product from the price list on the Lists sheet.' } },
    { id: 'qty', rects: body(3), allow: 'whole', operator: 'between', value1: '1', value2: '500', ignoreBlank: true, inCellDropdown: false,
      alert: { style: 'stop', title: 'Quantity', message: 'A whole number from 1 to 500. Larger orders go through the sales desk.' } },
    { id: 'discount', rects: body(5), allow: 'decimal', operator: 'between', value1: '0', value2: '0.2', ignoreBlank: true, inCellDropdown: false,
      alert: { style: 'warning', title: 'Discount', message: 'Discounts above 20% need a manager\'s approval. Keep it anyway?' } },
    // A relative bound: =A2 on G2 reads A5 on G5, as Excel moves it.
    { id: 'ship', rects: body(6), allow: 'date', operator: 'greaterOrEqual', value1: '=A2', ignoreBlank: true, inCellDropdown: false,
      alert: { style: 'stop', title: 'Ship by', message: 'The ship-by date has to be on or after the order date in column A.' } },
  ]
  doc.get('Orders').freeze = { rows: 1, cols: 0 }

  const BAND = { bold: true, fill: '#e2e8f0', color: '#0f172a' } as const
  const MONEY = { numFmt: '$#,##0.00' } as const
  const PERCENT = { numFmt: '0%' } as const
  const TOTAL = { bold: true, fill: '#eef2ff', color: '#1e1b4b' } as const

  type Entry = Record<string, CellFormatEntry>
  const across = (sheet: string, cols: string, row: number, entry: CellFormatEntry): Entry =>
    Object.fromEntries([...cols].map((c) => [`${sheet}${c}${row}`, entry]))
  const down = (col: string, from: number, to: number, entry: CellFormatEntry): Entry =>
    Object.fromEntries(Array.from({ length: to - from + 1 }, (_, i) => [`${col}${from + i}`, entry]))

  const formats: Entry = {
    ...across('', 'ABCDEFGH', 1, BAND),
    ...down('E', 2, LAST, MONEY), ...down('F', 2, LAST, PERCENT), ...down('H', 2, LAST, MONEY),
    ...across('', 'ABCDEFGH', LAST + 2, TOTAL),
    [`H${LAST + 2}`]: { ...TOTAL, ...MONEY },
    ...across('Lists!', 'ABD', 1, BAND),
    ...down('Lists!B', 2, 7, MONEY),
  }
</script>

<section class="wrap flex flex-col flex-1 min-h-0">
  <SvSheet document={doc} height="100%" rows={22} columns={9} columnWidths={{ A: 110, C: 150, G: 110 }} {formats} />
  <p class="note shrink-0">
    Rows 10 to 17 are open. Pick a Region and a Product from the arrows, type
    a Qty, and the price and total fill in. Try <strong>900</strong> in Qty
    (a Stop), <strong>0.35</strong> in Discount (a Warning you can keep),
    or a Ship by date earlier than the order date. Data &gt; Data Validation
    shows the rule behind the active cell.
  </p>
</section>

<style>
  .wrap { gap: 8px; }
  .note { margin: 0; font-size: 13px; line-height: 1.6; color: var(--sg-muted, #64748b); }
</style>

View this example on GitHub

More Spreadsheet examples

• Spreadsheet + Ribbon bar - The whole Excel surface as one component, <SvSheet workbook={wb} />: a six-tab ribbon (Home, Insert, Formulas, Data, Review, View), the Name Box and fx bar, sheet tabs and the Sum / Average / Count status bar. Format cells, merge them, comment on them, validate what goes in, colour them by rule, filter the region, protect the sheet; every button is the same call as its shortcut, so Ctrl+B and the Bold button cannot drift. A two-sheet P&L with the formats travelling in the document.
• Spreadsheet + formulas - Real formula engine inside the grid: cell refs (A1), ranges (A1:A10), SUM / AVG / IF / COUNTIF / ROUND, arithmetic, string concat, cycle detection.
• Per-cell custom borders (KPI) - Editable KPI scorecard. spreadsheetLayout paints spreadsheet-style per-edge custom borders via an absolute-positioned overlay (no border-collapse conflicts). Edit any quarter or target - the borders re-derive: green double = beat target, blue solid = hit, amber dotted = near miss, red dashed = bad miss; row champion gets a colored full frame.
• Cell merging (spreadsheet shell) - A real invoice rendered on an Excel-style shell: A / B / C / D / E column letters across the top, row numbers down the left. Brand band, bill-from / bill-to address blocks, meta block, line items, totals, notes, signatures - all assembled from MergeSpec + CellBorderSpec. Editable Qty / Rate / addresses / notes; totals recompute live.
• HyperFormula integration - Full HyperFormula engine wired into the grid as a peer-optional dep. Editable spreadsheet with A1-style cell refs, dozens of formulas across math (SUM / SUMIF), lookup (VLOOKUP / INDEX-MATCH), text (CONCAT / UPPER), date (TODAY / DATEDIF), logical (IF nests), financial (PMT / IRR / NPV), statistical (AVERAGE / MAX / RANK).