Non-volatile replacement for "Index" in a range

count von count

New Member
Joined
Nov 9, 2007
Messages
29
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!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,749
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 9, 2007
Messages
29
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top