INDEX return CYCLE error when ranges overlap

[beikov]

Description

I'm trying to refer to columns by index within the current row. Usually in Excel, I would use INDIRECT(ADDRESS(ROW(); column)) to do this. I tried to use INDEX(A1:ZZ99; ROW(); column) but this does not work as intended.

Use some data e.g.:

["Abc", 1234, "=INDEX(A1:C6; ROW(); 2)"]

Steps to reproduce

Try to use any column number (1, 2, 4) and the value for the formula always will be [object Object].

I just tried this in https://codesandbox.io/s/github/handsontable/hyperformula-demos/tree/0.5.x/basic-usage?from-embed=&file=/src/data.js

[wojciechczerniak]

[object Object] has an error and the details. First was the syntax ; but default config is comma ,. Second was the #CYCLE, I had to move the INDEX out of its range.

data = [["Abc", 1234, "=INDEX(A2:B6, ROW(), 2)"]

or

data = [
  [null, null, "=INDEX(A2:C6, ROW(), 2)"],
  ["Abc", 1234]
]

[wojciechczerniak]

The difference between HF and Excel is mentioned in the docs https://handsontable.github.io/hyperformula/guide/known-limitations.html and I guess that is the reason why you get a different results:

For cycle detection, all possible dependencies between cells are taken into account, even if some of them could be omitted after the full evaluation of expressions and condition statements.

[beikov]

Thanks for the quick answer. How would you suggest should I implement formulas like these that need to refer to cells of the current row?

[izulin]

Thanks for the quick answer. How would you suggest should I implement formulas like these that need to refer to cells of the current row?

Within the current implementation, if the cell is contained inside the range it refers to, it will create CYCLE error. My approach would be to truncate the range (if the column where the formula is is the first or last) not to contain the problematic column, or to split the range into two sub-ranges and combine the lookup with IF() function.

[beikov]

Thanks for the suggestion. I will try to workaround this for now.

[wojciechczerniak]

I'm relabeling this as To Be Discussed, and not a Bug. It's a known limitation / implementation choice mentioned in: https://handsontable.github.io/hyperformula/guide/known-limitations.html

May be a duplicate of #336, but I would keep it as a different use case scenario for now