Volatile Functions and closed workbooks.

[shrivallabha]

Here's thread I have started on MrExcel:
http://www.mrexcel.com/forum/showthread.php?t=595911


For last few days, I have been coming across the term "Volatile" often. Now the question I have is related to INDIRECT function.

1. INDIRECT being VOLATILE will be calculated every time when the application will be calculating even if the ranges to which it references are not changed.

2. VOLATILE behaviour applies to only OPEN workbooks.

3. So the result (since it is referenced from CLOSED workbook) won't get calculated correctly as application will only loop through only OPEN workbooks. So it ends up with #REF error.

4. So does it mean that above analogy (only if correct) can be extended to other VOLATILE functions as well?

5. And does it finally mean:
VOLATILE functions evaluate based on the data available in open workbooks whereas, the NON-VOLATILE functions establish some kind of link which works even when the workbook is closed.

Is this correct understanding?
I realized that some of the guys here, don't visit MrExcel often. But in the past, I have been helped a great deal here by them. So I'd like to hear from you guys as well.

[shrivallabha]

Bump ^

[Aflatoon]

There is no correlation between volatility and open/closed workbooks, so I am not entirely sure that I understand the question.
INDIRECT does not work with closed workbooks; it is also volatile. The two facts are unrelated.
SUMIF does not work with closed workbooks; it is not volatile. Again the two facts are unrelated.

The only way in which volatility relates to open workbooks is that any open workbook can cause volatile functions in any other open workbook to recalculate. This is only because closed workbooks cannot do anything.

[Bob Phillips]

There is no correlation between volatility and open/closed workbooks, so I am not entirely sure that I understand the question.
INDIRECT does not work with closed workbooks; it is also volatile. The two facts are unrelated.
SUMIF does not work with closed workbooks; it is not volatile. Again the two facts are unrelated.

The only way in which volatility relates to open workbooks is that any open workbook can cause volatile functions in any other open workbook to recalculate. This is only because closed workbooks cannot do anything.

Which is what I believe he was told at the other place, which underlines the waste of effort involved in dealing with cross-posting.

[Aflatoon]

Agreed. The message did not seem to have been received though, so I thought that perhaps some reinforcement might prove beneficial. ;)

[Bob Phillips]

I was just amazed to see it being bumped today when he had enough response over there. I just don't understand the thinking there.

[Aflatoon]

Perhaps the OP felt satori had not been achieved and sought further answers? And perhaps he will choose to enlighten us in due course.

[shrivallabha]

There is no correlation between volatility and open/closed workbooks, so I am not entirely sure that I understand the question.
INDIRECT does not work with closed workbooks; it is also volatile. The two facts are unrelated.
SUMIF does not work with closed workbooks; it is not volatile. Again the two facts are unrelated.

The only way in which volatility relates to open workbooks is that any open workbook can cause volatile functions in any other open workbook to recalculate. This is only because closed workbooks cannot do anything.
I see reply from both of you guys whom I respect highly. I wasn't sure Aflatoon visited MrExcel and Bob (The name is in banned list [I had searched for your name and I know that is none of my business]). So I could not say "Bob and Aflatoon" don't visit MrExcel.

Aflatoon, the SUMIF function sums it for me as it is NON-VOLATILE and doesn't work. Rory had replied there but this was the piece {i.e. NON-VOLATILE function not working with CLOSED workbooks.}

Can you tell me something about this observation?

Create 2 workbooks named Test.xlsx and Test1.xlsx. Refer Test1's range A1 on Sheet1 in workbook Test using both functions. Now close the workbook Test1. Recalculate, INDIRECT will show error [#REF]. Now copy Test1.xlsx to another location. Open it and change Sheet1!A1 value. Now the INDIRECT function will show the changed value whereas INDEX will keep holding onto the original reference.

[Aflatoon]

In what respect? INDIRECT does not work with closed workbooks, as has already been belaboured.
INDEX will hold a direct reference to the range on the other sheet in the other workbook, and hence include the full path to it. INDIRECT will only have as much of a reference as you give it, so if you only pass a workbook name (not path) it will refer to any open workbook with that name.

[shrivallabha]

Thanks. Now it is clear. To begin with, I was conceited with my thinking of INDIRECT (Volatile) and INDEX (Non Volatile) and their evaluation of cell addresses.

It was SUMIF 'black swan' which I needed and the final explanation makes it clear.

With that said, I'd prefer(don't have better word) being called dumb or stupid than not asking my doubt.

Thanks a lot for all your help. You all have been kind enough, as always.