Cross-sheet Formulas

External sheets are datasets available to the Formula engine; they are not additional mounted grids. Sheet ids are case-insensitively unique. Quote an id containing spaces, as in 'Product List'!A1:B2, and give every sheet stable, unique row ids.

Complete Orders and Product List fixture

Orders maps A=sku, B=qty, C=catalogPrice, D=total. Product List maps A=sku, B=price.

import { FormulaPlugin } from '@revolist/revogrid-pro';

const orderColumns = [
  { prop: 'sku', name: 'SKU' },
  { prop: 'qty', name: 'Quantity' },
  { prop: 'catalogPrice', name: 'Catalog price' },
  { prop: 'total', name: 'Total' },
];
const productColumns = [
  { prop: 'sku', name: 'SKU' },
  { prop: 'price', name: 'Price' },
];
const productRows = [
  { id: 'product-1', sku: 'A-100', price: 10 },
  { id: 'product-2', sku: 'B-200', price: 25 },
];
const orderRows = [{
  id: 'order-1',
  sku: 'B-200',
  qty: 2,
  catalogPrice: '=VLOOKUP(A1,\'Product List\'!A1:B2,2,FALSE)',
  total: '=B1*C1',
}];

grid.plugins = [FormulaPlugin];
grid.columns = orderColumns;
grid.formulaWorkbook = {
  sheetId: 'Orders',
  rowIdProp: 'id',
  externalSheets: [{
    id: 'Product List',
    rowIdProp: 'id',
    columns: productColumns,
    rows: productRows,
  }],
};
grid.source = orderRows;

Expected result: C1 displays 25 and D1 displays 50. The raw formulas remain in orderRows.

INDEX and MATCH

Use INDEX/MATCH when the lookup and return ranges should be explicit:

orderRows[0].catalogPrice =
  '=INDEX(\'Product List\'!B1:B2,MATCH(A1,\'Product List\'!A1:A2,0))';
grid.source = [...orderRows];

Expected result: C1 still displays 25, then D1 displays 50.

The tested lookup subset is LOOKUP, VLOOKUP, HLOOKUP, and INDEX/MATCH. XLOOKUP and arbitrary Excel workbook features are not supported.

Load an external sheet later

Mount Orders with no Product List, then register it when the application finishes loading:

grid.formulaWorkbook = {
  sheetId: 'Orders',
  rowIdProp: 'id',
  externalSheets: [],
};
grid.source = orderRows;

const plugins = await grid.getPlugins();
const formulas = plugins.find(plugin => plugin instanceof FormulaPlugin);
if (!formulas) throw new Error('FormulaPlugin is not mounted');

formulas.upsertFormulaSheet({
  id: 'Product List',
  rowIdProp: 'id',
  columns: productColumns,
  rows: productRows,
});

Expected result: C1 first renders the mounted missing-sheet loading state, then changes to 25; D1 changes to 50. The synchronous backend calculator reports a missing sheet as an error instead of waiting.

Update an external cell

formulas.updateFormulaSheetCell('Product List', 'product-2', 'price', 30);

Expected result: C1 changes from 25 to 30, and D1 changes from 50 to 60. The row id selects product-2; the final argument uses the column property price, not the A1 letter.

Workbook lifecycle API

Method	Use it when
setFormulaWorkbook(config)	Replacing the mounted sheet identity or the complete set of external sheets.
upsertFormulaSheet(sheet)	Adding a late-loaded sheet or replacing one dataset.
updateFormulaSheetCell(sheetId, rowId, prop, value)	Applying one known external cell update.
removeFormulaSheet(sheetId)	Making one external sheet unavailable. Dependent mounted formulas return to the missing-sheet loading state.
setFormulaWorkbook(null)	Detaching the mounted grid from workbook context.

Next steps

Previous: Formula UI and Helpers · Next: Async Formula Functions · Deeper reference: Formula API