Replace ={Sheet1!$A:$A} with a named range?

[jablko]

Is it possible to replace the formula ={Sheet1!$A:$A} with a named range i.e. =myRange? Is there a trick I don't yet know?

The following:

import { HyperFormula } from "hyperformula";
// Named expressions can be used in any formula by referencing their names. Use
// them anywhere you would normally use a cell reference, range, or constant
// value.
// https://hyperformula.handsontable.com/guide/named-expressions.html#using-named-expressions-in-formulas
const row = [
  "={1;2}", // Target column
  "=myCell", // Works: named cell
  "={Sheet1!$A:$A}", // Works: range
  "=myRange", // Fails: identical range
  "=TRANSPOSE(TRANSPOSE({Sheet1!$A:$A}))", // Works: array formula
  "=myFormula", // Fails: identical array formula
  "=TRANSPOSE(TRANSPOSE(myRange))", // Fails: identical array formula
  "=SUM(myRange)", // Works: aggregate range
  "=SUM(myFormula)", // Works: aggregate formula
];
const options = {
  licenseKey: "gpl-v3",
  useArrayArithmetic: true,
};
const namedExpressions = [
  { name: "myCell", expression: "=Sheet1!$A$1" },
  { name: "myRange", expression: "={Sheet1!$A:$A}" },
  { name: "myFormula", expression: "=TRANSPOSE(TRANSPOSE({Sheet1!$A:$A}))" },
];
const hf = HyperFormula.buildFromArray([row], options, namedExpressions);
const start = { sheet: 0, row: 0, col: 0 };
const end = { sheet: 0, row: 0, col: row.length - 1 };
const result = hf.getRangeValues({ start, end });
console.log(result);

Prints:

[
  [
    1,
    1,
    1,
    DetailedCellError {
      value: '#VALUE!',
      address: undefined,
      type: 'VALUE',
      message: 'Cell range not allowed.'
    },
    1,
    DetailedCellError {
      value: '#VALUE!',
      address: undefined,
      type: 'VALUE',
      message: 'Cell range not allowed.'
    },
    DetailedCellError {
      value: '#VALUE!',
      address: undefined,
      type: 'VALUE',
      message: 'Cell range not allowed.'
    },
    3,
    3
  ]
]

[sequba]

Hey, @jablko, thanks for reaching out.

As you observed yourself, it is possible to define a named expression that corresponds to a range:

hf.addNamedExpression('myRange', '=Sheet1!$A:$A');

And it seems to be working inside formulas:

hf.setCellContents(addr, '=SUM(myRange)');

But for some reason, it returns an error when you try to use it directly in a cell:

hf.setCellContents(addr, '=myRange'); // ERROR: 'Cell range not allowed.'

It looks like a bug in HyperFormula that we need to examine closely to determine why it works this way. Thank you for reporting it.

[sequba]

Let's continue the discussion on this issue here: #1533