Trying to rid my file of slow INDIRECT functions, but stuck

[d0rian]

I've recently been reading how much frequent use of volatile functions (esp INDIRECT) slows down calculation speed, which might be the source of my file's bad lag, since I use it a lot.

I've managed to eradicate it from most of my formulas, but I'm stick with a dozen columns where I can't figure out how to get rid of it. Below is an example of one of the columns. Here's the problem:

- Column EJ needs to run a calculation on a set of rows that changes as I pull new values into the file. In this instance, it's counting things starting from row 4 down to the row above the cell with the formula (ROW()-1).
- Columns DW and DX are helper cells that specify the first and last row of the range...so in this case, the INDIRECT function will return "EI4:EI15"
- The data in the rest of my file updates frequently, and therefore DW and DX update with different row-ranges as well...which is why I'm using INDIRECT in the first place...because I need the formula in EJ to be dynamic / able to adapt to a changing range.

I haven't included the full formula below because it's very long, and moreover I have a dozen other columns that use different formulas with slightly different iterations of the INDIRECT function...so I didn't want posters to look to provide a solution for this one particular use case, because I'm relying on this type of syntax in a dozen different places. So hoping that someone might just be able to tell me in general terms whether there's a better way to refer to a dynamic range than what I'm currently doing.

 

[mrhstn]

I think you might be able to use INDEX for such dynamic calculations.

In place of your indirect function you could use

INDEX(EI:EI, DW16):INDEX(EI:EI, ROW()-1)

You may wish to have the column dynamic also, so that could be achieved by expanding the EI:EI range and calculating the column number relative to the start.