Forum Discussion
Can INDIRECT be used to dynamically alter the second range reference in a Named Range
I have a workbook that retrieves data from another spreadsheet, the latter being downloaded from my brokerage. Occasionally the number of rows in the downloaded sheet goes up or down, based on buying...
I convinced Kevin Lehrbass to shift from OFFSET to INDEX here:
https://www.myspreadsheetlab.com/dynamic-ranges-using-index-function/
https://www.myspreadsheetlab.com/dynamic-ranges-using-index-function/
I agree that if volatile functions are avoidable then we should avoid them 🙂 Twifoo
Often the non volatile formula alternative has more complex syntax but for serious development we should take the time to avoid volatile functions whenever possible.
Obviously, as SergeiBaklan mentioned above, there are times when NOW() (volatile) is unavoidable.
Thanks to Twifoo for various non volatile alternative solutions on my blog this year!