Workbooks: several sheets that read each other

The formula engine parsed Orders!A1 and 'Price list'!A1:C9 from the day it was promoted. This is the model those references point at.

import { createWorkbook, SvSheetTabs, setWorkbook } from '@svgrid/enterprise'

The model

const wb = createWorkbook([
  { name: 'Budget',  cells: [['10'], ['20'], ['=SUM(A1:A2)']] },
  { name: 'Summary', cells: [['=Budget!A3'], ['=Budget!A3*2']] },
])

wb.getValue('Summary', 1, 0)     // 60
wb.setRaw('Budget', 0, 0, '100')
wb.getValue('Summary', 1, 0)     // 240

Editing Budget!A1 reaches Summary!A2, which never mentions A1: the dependency graph spans sheets, so a change propagates as far as it needs to and no further.

Cells hold raw text and nothing else. The computed value, the dependency edges and the display string are all recomputed from it, so there is one copy of the truth and no way for a cached value to disagree with the formula above it.

Defined names

wb.names is the workbook's Name Manager. A name stands for a cell or a range, and a formula uses it in place of the address:

wb.names.define('TaxRate', '=Inputs!$B$3')
wb.names.define('Sales', '=Orders!$I$2:$I$25')

'=Subtotal*TaxRate'      // one cell
'=SUM(Sales)'            // the whole range
'=SUMIF(Orders!B2:B25, "North", Sales)'

A name that refers to a range is that range wherever a range would do: inside SUM, COUNTIF, VLOOKUP, INDEX. In scalar position it collapses to its top-left cell, as a range does in Excel.

Edits behind a name propagate. =Subtotal*TaxRate recomputes when the cell behind TaxRate changes, because the dependency graph follows the name to its cell when the formula is evaluated. Redefining or removing a name drops every cached value, so the next read recomputes against the new definition; there is nothing to call afterwards.

A name may be defined as another name (Sales -> Q3Sales). A circular chain reads as #NAME? rather than hanging.

Rows and columns inserted or deleted through applyStructuralEdit move a name with the cells it refers to, and only then: Sales above follows an insertion on Orders and ignores one on Summary. The same rule holds for formulas, so =Orders!B2 on Summary stays put when Summary gains a column while =B2 beside it moves.

Blank versus #REF!

A sheet has no size in this model beyond what has been typed into it, so reading past the written area returns blank. =SUM(A1:A100) over a twelve-row sheet is an ordinary thing to write, and returning #REF! for the empty rows would poison the total.

#REF! is reserved for the two cases that really are broken: a sheet that does not exist, and a negative index, which is what a reference shifted off the top by a delete becomes.

A range that contains its own formula is a circular reference, so =SUM(A1:A100) sitting in A3 reports #CYCLE!. Excel agrees.

Structural edits reach the whole workbook

wb.applyStructuralEdit('Orders', { kind: 'insertRows', at: 0, count: 1 })

A formula on another sheet holding =Orders!A3 becomes =Orders!A4, because the row it pointed at moved. An unqualified reference on another sheet is left alone: its own sheet's geometry did not change, and shifting it would break it.

Sheet tabs

<script>
  import { SvSheetTabs, setWorkbook } from '@svgrid/enterprise'

  let version = $state(0)
  const wb = createWorkbook(...)
  setWorkbook(wb, () => (version += 1))
</script>

<SvSheetTabs workbook={wb} {version} onChange={() => (version += 1)} />

Pass version back IN as well as out. A Workbook is a plain object rather than $state, so reading workbook.sheets creates no reactive dependency and the strip would render once and then show whatever the sheet list was at mount - it would not notice Ctrl+PageUp moving the active sheet underneath it. The strip bumps its own counter for its own buttons, so version is only needed for mutations that come from somewhere else: the shortcuts, or app code calling addSheet.

Click to switch, double-click or F2 to rename, drag to reorder, + to add. The strip owns no state of its own, so the tabs and the keyboard shortcuts cannot disagree about which sheet is active. A duplicate or invalid name is reported rather than silently ignored, and Delete is greyed on the last sheet, because the workbook refuses to remove it. Deleting a sheet that holds anything asks first, as Excel does; an empty one goes at once.

The right-click menu also has Duplicate, Hide and Unhide. The strip keeps none of that itself: pass hidden (the names not to draw) and answer onHide, onUnhide and onDuplicate, which the shell does through its document (sheetHidden per sheet, doc.duplicate(name)), and Duplicate is only offered when onDuplicate is given. wb.copySheet(from, to?) copies a sheet's cells into a new sheet right after it, named Excel's "Name (2)" unless told otherwise; the copy's formulas read their own sheet. Hide is refused on the last sheet showing, and the shortcuts below step over hidden sheets when setWorkbook is told which they are: setWorkbook(wb, onChange, (name) => doc.get(name).sheetHidden).

These decline with no workbook attached, so a single-sheet grid leaves Ctrl+PageDown to the browser's own tab switching. They do not wrap at either end, matching Excel: pressing again on the last sheet leaves you there.

Goal Seek

"What rate makes the payment 1500?" You know the answer you want and one cell you are willing to change; the solver finds it.

import { goalSeekCell } from '@svgrid/enterprise/sheet'

const result = goalSeekCell(
  workbook,
  { sheet: 'Model', row: 8, col: 1 },   // the formula cell
  { sheet: 'Model', row: 2, col: 1 },   // the input to change
  1500,                                  // the target
)

if (result.converged) {
  // Ask first, then apply. Excel does.
  workbook.setRaw('Model', 2, 1, String(result.value))
}

The input cell is restored before goalSeekCell returns, whatever the outcome. The solver writes to it dozens of times while searching, and leaving the last probe behind would be worse than not running: it is also what makes Excel's "Goal Seek found a solution, OK or Cancel" possible. Apply result.value once the user says yes.

What it does when it cannot

converged: false still carries a value and a result: the closest attempt, so a dialog can show it rather than nothing. reason says what happened.

The method

Secant with a bisection fallback, not Newton. Newton needs a derivative, and the function here is "recalculate a spreadsheet", which has no analytic one. Secant approximates the slope from two evaluations, which is what a numerical derivative would cost anyway.

Bisection is the fallback because secant is fast but not safe: it can step to infinity on a flat stretch or oscillate at a kink. Once two inputs are known whose results straddle the target, bisection inside that bracket cannot diverge, only be slow.

The second probe scales with the guess rather than stepping a fixed amount. A step of 1 is enormous next to an interest rate of 0.05 and invisible next to a loan of 300000.

goalSeek is the bare solver over any function, if the thing you are solving is not a spreadsheet:

goalSeek((x) => x * x, 9, 1)   // { converged: true, value: 3, ... }

Saving the whole document

workbook.serialize() is the cells, the sheet order, the active sheet and the defined names. The spreadsheet shell keeps more beside them (formats, column widths, row heights, hidden lines, frozen panes, per sheet), and getState() / setState() on <SvSheet>, or createSheetDocument on its own, carry all of it as one JSON document. See Saving and restoring.

More examples

Autosave: a document that survives a reload

A project tracker that keeps itself in localStorage: onChange fires once per tick with every kind of change, the demo debounces it and writes getState() as JSON, and the next visit rebuilds the document with createSheetDocument({ state }) and hands it to . Add a comment, bold a row, hide a column, type a task, reload: it is all back, rules and merged title and frozen rows included. Reset puts the shipped document back with setState().

What-if analysis: Goal Seek

A pricing model with three scenarios side by side and Excel's Goal Seek over it: Data -> Goal Seek, set the profit cell to 20,000 by changing the price, and the solver (secant with a bisection fallback) finds it. The sheet does not move until the status dialog says "found a solution" and you press OK; OK writes through the grid's command context, so it is one Ctrl+Z.

See also

• Spreadsheet formulas
• Excel keyboard shortcuts