Request: function INDIRECT

[ghost]

For a project I'm trying to realize in ethercalc I need function INDIRECT or a workaround. Please add this function to ethercalc.

[eddyparkinson]

INDIRECT workaround INDEX is maybe the closest. All the lookup formulas are listed in the help under LOOKUP

Added an "enhancement" label

[azeorb]

INDEX is not a real workaround for INDIRECT. INDIRECT can take a cell which contains text and read the defined name or cell reference within it. For example, if the cell at A1 has text "B2", INDIRECT(A1) will give you the value of the cell B2. That can't readily be replicated with any combination of existing ethercalc functions.

[rbnmx]

@azeorb I didn't tried yet but think that INDEX could be used in a workaround to substitute index. Another approach could be to create a custom function similar to EVALUTE, a proof of concept custom function for Google Sheets that use the formula module of Ethercalc -> http://stackoverflow.com/a/40733711/1595451

[azeorb]

@rubenrivera the reason why INDEX cannot substitute for INDIRECT is that the excel INDIRECT takes a cell, reads the value, and interprets it as a cell reference. There are two major and non-obvious issues:

1) Ethercalc currently exposes no method that can take a variable string like "A1" and generate a reference to the cell A1. That is a pre-requisite for INDIRECT and a few other related lookup functions.

2) The dependency structure is not reflected in the formula. In the case of INDEX, the range argument covers every possible reference of the result. For example, in the formula =INDEX(A1:B2,__,__) the only possible non-error values are the values in A1, A2, B1, B2. It is an error to reference outside of the range. Now suppose you had a sheet like this:

  | A             | B |
1 | B1            | 3 |
2 | =INDIRECT(A1) |   |

Because of how the formula is structured, the value of A2 will be the value of B1. If you change B1 to 5 then A2 will show 5. However, unlike in the case with INDEX, the INDIRECT formula does not contain an actual reference to the final result cell. So you would either need to make it force a recalc every single time, akin to how NOW() works, or encode the dependency structure in a different way. Both approaches require some underlying changes that cannot be feigned with normal ethercalc functions.

[rbnmx]

@azeorb : I didn't say that INDEX is an equivalent of INDIRECT but that it could be used to build a workaround. I agree that it would be great if Ethercalc includes all the OpenFormula functions.

The workaround that I'm thinking on involves to insert a column to hold the row indexes, to insert another column to hold the column labels (A,B,C,...), functions like LEFT and RIGHT to get the reference coordinates, MATCH to convert the column labels to an INDEX and VLOOKUP. Obviously this will move the original references so the original reference strings should the updated accordingly.

[azeorb]

@rubenrivera to be 100% clear, you cannot use any of the existing functions to build a fake implementation of INDIRECT. The mechanics undercut the dependency resolution logic. It has to be directly built into the underlying library.

@eddyparkinson any thoughts on where to begin?

[eddyparkinson]

@azeorb INDIRECT would be a good addition. Pull requests are welcome.

All the formulas are in this file: https://github.com/marcelklehr/socialcalc/blob/master/formula1.js

Formula help is in https://github.com/marcelklehr/socialcalc/blob/master/socialcalcconstants.js