Why is INDEX slow?
We have been using SmartSheet for many years and have developed our entire project management around it. Every production sheet is derived from the same template. Thus, every production sheet has the same columns and many of those columns have formulas. These sheets now use summary fields, some of which derive their values from the sheet fields. Each formula refers to either columns in the same row (using @row) or entire other columns (or both).
All of this has been working well to date.
Recently, we attempted to expand our implementation with extra columns to obtain information from the row that is nominated as the predecessor row. It is here that we have run into performance issues when opening, saving and copy/pasting rows in our test sheets.
The two new columns are:
[Predecessor Row] with a formula that extracts the row number from the first predecessor in [Predecessors] (e.g. 215 if [Predecessors] is "215FS-3d,245SS") and returns 0 if [Predecessors] is null.
[Predecessor %Complete] with the formula =IF([Predecessor Row]@row = 0, "", INDEX([%Complete]:[%Complete], [Predecessor Row]@row)).
We then use [Predecessor %Complete] in the calculation that informs a staff member (on his or her task report) that a task is ready to be worked on (because its predecessor is complete) or is pending (because its predecessor is not yet complete).
After a deal of investigation, we have narrowed our speed issue down to INDEX.
We have already attempted reducing the INDEX's range down to being from the first row down to the current row (on the assumption that all predecessor references point to an earlier row). This didn't change the speed.
So, is there a way to take a (dynamically obtained) row number and use it to obtain the value from a given cell on that nominated row. If INDEX is the only mechanism, then why is it so slow and how can it be sped up?