hyperformula-named-references-bug

https://github.com/handsontable/hyperformula/issues/1208

by thilgen

HTML

<!DOCTYPE html>
<html>

<head>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.css" />
  <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/handsontable/dist/handsontable.full.min.js"></script>
  <link rel="stylesheet" href="styles.css" />

</head>

<body>
  <div class="tt">
    <p>Checking Ledger</p>
    <div id="checking_ledger"></div>
    <p>Accounts</p>
    <div id="accounts"></div>
  </div>
  <script src="index.js"></script>
</body>

</html>

CSS

.tt .handsontable colgroup col:nth-child(1) {
  min-width: 50px;
  max-width: 50px;
}

.tt .handsontable colgroup col:nth-child(2) {
  min-width: 150px;
  max-width: 150px;
}

.tt .handsontable colgroup col:nth-child(3) {
  min-width: 200px;
  max-width: 200px;
}

.tt .handsontable colgroup col:nth-child(4) {
  min-width: 75px;
  max-width: 75px;
}

.tt div {
  margin-bottom: 5px;
}

.tt p {
  font-family: 'Courier New', Courier, monospace;
  font-weight: bold;
}

.handsontable tr td:nth-child(2) {
  background-color: rgb(184, 249, 174);
}

.handsontable tr td:nth-child(3) {
  background-color: rgb(184, 249, 174);
}

#accounts .handsontable tr td {
  background-color: rgb(184, 249, 174);
}

JavaScript

// https://github.com/handsontable/hyperformula/issues/1208

const hyperformulaInstance = HyperFormula.buildEmpty({
  licenseKey: 'internal-use-in-handsontable',
});

window.addEventListener('DOMContentLoaded', onDocLoaded, false);

function onDocLoaded() {
  createTables()
  styleTables()
  addNamedExpressions()
}

function addNamedExpressions() {
  hyperformulaInstance.addNamedExpression(
    'AccountLookup',
    "=IFNA(VLOOKUP(INDEX(checking_ledger!$A:$A, ROW()),accounts!$A:$B,2,FALSE()),\"\")",
    1
  )
}

function createTables() {
  accounts = createTable("accounts",
  ["Account ID", "Account Name"],
    [
      [23, "Office Supplies"],
      [13, "Rent"],
      [26, "Gas"],
      [20, "Maintenance"],
      [30, "Capital Improvements"],
    ]
  )
  checking_ledger = createTable("checking_ledger",
    ["Account ID", "Account Name", "Account Name (Named Expression)"],
    [
			[26, "=IFNA(VLOOKUP(INDEX(checking_ledger!$A:$A, ROW()),accounts!$A:$B,2,FALSE()),\"\")", "=AccountLookup"],
      [13, "=IFNA(VLOOKUP(INDEX(checking_ledger!$A:$A, ROW()),accounts!$A:$B,2,FALSE()),\"\")", "=AccountLookup"],
      [20, "=IFNA(VLOOKUP(INDEX(checking_ledger!$A:$A, ROW()),accounts!$A:$B,2,FALSE()),\"\")", "=AccountLookup"],
     ]
  )
}

function createTable(containerName, colHeaders, data) {
  var container = document.getElementById(containerName)
  container.hot = new Handsontable(
    container,
    {
      licenseKey: 'non-commercial-and-evaluation',
      rowHeaders: false,
      colHeaders: (0 != colHeaders.length) ? colHeaders : false,
      height: 'auto',
      fillHandle: false,
      formulas: {
        engine: hyperformulaInstance,
        sheetName: containerName
      },
      data,
    }
  )
  return container.hot
}

function styleTables() {
  const tables = [
    checking_ledger,
    accounts
  ].forEach(table => [
    table.updateSettings({
      cells(row, col) {
        const cellProperties = {};
        if (1 == col) {
          cellProperties.readOnly =...