Named Expression returns #CYCLE! in changes

[wojciechczerniak]

Description

When Named Expression is added the changes are calculated. With an absolute address, the changes will be calculated for real values that are part of the workbook. But when named expression contains relative address, it will be calculated relatively to sheet -1 where the named expressions are stored.

Named Ranges in XL and GS are limited to absolute addresses. We could validate expression if it contains only the absolute addresses. Which is fine, but limited to ranges. Named Expressions are much more.

Only Libre Calc has full Named Expressions support. And this works as expected, named expression is relative to the sheet where it was used.

This complicates the changes returned when a relative named expression is added and as a result, we get CYCLE error.

Credits: @budnix

Steps to reproduce

  it('basic usage with global named expression', () => {
    const engine = HyperFormula.buildFromArray([
      ['42'],
    ])

    const changes = engine.addNamedExpression('myName', '=Sheet1!A1+10', undefined)
    // [ ExportedNamedExpressionChange { name: 'myName', newValue: 52 } ]
    // OK! Calculation is based on Sheet1 

    expect(engine.getNamedExpressionValue('myName')).toEqual(52)
  })

  it('basic usage with global named expression', () => {
    const engine = HyperFormula.buildFromArray([
      ['42'],
    ])

    const changes = engine.addNamedExpression('myName', '=A1+10', undefined)
    // [ ExportedNamedExpressionChange { name: 'myName', newValue: DetailedCellError { error: [CellError], value: '#CYCLE!' } } ]
    // NOT OK! We get an error because address is relative

    expect(engine.getNamedExpressionValue('myName')).toEqual(52)
  })

[wojciechczerniak]

Currently relative named expressions are not supported.