Non-volatile replacement for "Index" in a range

[count von count]

Wow, I haven't posted here for 8 years... surprised I could remember my login!

I have a formula that finds a single value in another workbook:
=INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns) – where columns is a reference to a cell in the active workbook – e.g. input 3 to get the number from col 3 in the target workbook

Works fine and, being INDEX rather than OFFSET, is non-volatile in that it doesn't need the other workbook to be open.

I have another formula that does something similar but, rather than look up a value in column 3, it sums the values for columns 1-3:
=SUM (‘[FileName] Tab’! cell : INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns))

However, using INDEX in this way makes it volatile, returning #REF errors if the cell to which columns refers is changed without the target workbook being open.

This volatility is confirmed here, quote:

"Just because a function is volatile in one version of Excel does not mean it is volatile in all versions. Perhaps the best example of this is INDEX (see INDEX MATCH for further details), which was volatile prior to Excel 97. Microsoft still states this function is volatile, but this does not appear to be the case except when used as the second part of a range reference, for example $A$1:INDEX($A$2:A$10,4), will also cause the reference to be flagged as dirty when the workbook is opened only."

Source of quote: Volatile Functions: Talk Dirty to Me < Thought | SumProduct are experts in Excel Training: Financial Modelling, Strategic Data Modelling, Model Auditing, Planning & Strategy, Training Courses, Tips & Online Knowledgebase

So, my question (at last!) is, is there a way to achieve the sum-index result without the volatility?

Thanks!

 

[offthelip]

have you tried using the plus sign instead of SUM:
e.g =INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns) +INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns) +INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns)

 

[count von count]

have you tried using the plus sign instead of SUM:
e.g =INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns) +INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns) +INDEX (‘[FileName] Tab’! cell : cell, #rows, #columns)

No, because the value entered in the cell to which #columns refers could be a number from 1 - 12 (it's adding x months of data). So your suggestion works for 3 months, but not for any other number of months.