# Data validation: lists, bounds and messages A rule that keeps a bad entry out at the keyboard: a dropdown of the allowed values, a number between two bounds, a date that has to follow another cell, a formula of your own. What the rule says, what the user sees when it is broken, and how to ship a sheet with the rules in place. [Getting started](https://svgrid.com/docs/help/sheet/start/) shows the document a rule lives in. The examples share an order form and the lookup for formatting rectangles. ```svelte ``` ## A rule A rule is an entry in a sheet's `validation` list: the rectangles it covers, what it allows, the bounds, and what happens when an entry breaks it. This one allows a whole number from 1 to 500 in Qty and refuses anything else: ```svelte ``` Type `900` into a Qty cell: the alert shows the title and the message, Retry reopens the cell with the entry still in it, Cancel drops it. The entry never reached the workbook. `ignoreBlank` lets a cell be emptied without an alert. `allow` is one of `any`, `whole`, `decimal`, `list`, `date`, `textLength` or `custom`; `operator` is `between`, `notBetween`, `equal`, `notEqual`, `greater`, `less`, `greaterOrEqual` or `lessOrEqual` and defaults to `between`. A bound is text as it would be typed, or a formula starting with `=`. ## A list with a dropdown `allow: 'list'` with `inCellDropdown: true` draws an arrow on the active cell; the arrow, or `Alt+Down`, drops the choices, arrows and Enter pick, and a pick is one undo. A typed entry is matched against the list without regard to case. The list is a comma list, or a range on any sheet, so adding a product to the Lists sheet adds it to the dropdown: ```svelte ``` A name works where a range does (`value1: '=Products'` after `wb.names.define('Products', '=Lists!$A$2:$A$5')`), which keeps the rule readable in the dialog. ## Stop, or warn `alert.style` is `stop` or `warning`. A Stop refuses the entry. A Warning asks whether to keep it and Yes lets it through, for a bound that is a policy rather than a fact: ```svelte ``` Type `35%` into a Discount cell and answer Yes: the cell shows 35%, and Circle Invalid Data (below) will find it. (A plain `35` in a cell formatted `0%` is 35% as well, Excel's automatic percent entry; the rule judges the value the entry lands as, 0.35 either way.) ## A bound that reads another cell A formula bound is evaluated in the sheet. `=$B$1` as a maximum follows B1; a relative `=A2` on the rule's first row reads the same row's column A on every row, as Excel moves it, so a ship-by date has to be on or after the order date beside it: ```svelte ``` Dates are `yyyy-mm-dd` text in the sheet; `allow: 'date'` reads the entry and the bound as dates. ## A formula of your own `allow: 'custom'` takes a formula written for the top-left cell of the rule's rectangle and moves it to each cell; TRUE, or a number other than 0, allows the entry. The cell being checked reads as the entry, which is not written yet, so a rule can refer to the cell it guards: ```svelte ``` Type `25` into the Pro seat row's Qty: refused. `30`: accepted. Any quantity on the Onboarding row: accepted. ## The message under the cell `input` gives a rule a title and a message that show in a small box under the cell while it is selected, Excel's Input Message, which tells the user the rule before they break it: ```svelte ``` ## What a rule does not check Only a typed entry is checked, as in Excel: a paste, a fill, `Ctrl+D` and a write from code land as they are. Data > Data Validation > Circle Invalid Data draws Excel's red oval on every cell under a rule whose current contents break it, whatever put them there, and Clear Validation Circles takes the ovals off; from code they are `act('circle-invalid')` and `act('clear-circles')`. The circles are worked out again on every repaint, so a corrected cell loses its oval at once. ```svelte sheet?.act('circle-invalid')}>Circle invalid data sheet?.act('clear-circles')}>Clear circles ``` ## Where the rules go Data > Data Validation opens on the rule at the active cell and puts one over the selection; Clear All takes a rule off a selection. A rule written from the dialog is the same object as one written from code. The rules are per sheet, move with an insert or a delete and drop when their cells go, ride in `getState()` as `validation`, report `{ kind: 'validation' }` on `onChange`, and go into the `.xlsx` as Excel's own, so the file opens in Excel with the dropdowns working. > Live demo: Order intake: data validation - https://svgrid.com/demos/461-order-intake-validation/ > Live demo: Data validation (dropdowns) - https://svgrid.com/demos/209-data-validation/ ## See also - [Formatting](https://svgrid.com/docs/help/sheet/formatting/) - conditional formatting, the rule that colours a value rather than refusing it. - [Review and share](https://svgrid.com/docs/help/sheet/review/) - protection, for cells nobody should type into at all. - [Formulas](https://svgrid.com/docs/help/sheet/formulas/) - what a formula bound can say. - [The spreadsheet shell](https://svgrid.com/docs/help/cells/spreadsheet-shell/) - the dialog, `ruleAt`, `checkEntry` and `listChoices` for a shell of your own. --- SvGrid is the Svelte 5 data grid (`npm install @svgrid/grid`, MIT). This page: https://svgrid.com/docs/help/sheet/validation/ . All docs: https://svgrid.com/llms.txt