excel triggering a calculate when it shouldn't

[sark666]

I have had this a few times in the past, but I have never figured out exactly what is causing it. I have a sheet that has an extract imported that is somewhat large and now any time I populate any cell a recalculation is being triggered.

If you put =sum(a1:a5) in a cell, excel should calulate this, but if you put 'Hello' in a cell, no calculation should be called. Well, if no formula is referencing that cell anyway.

With this workbook, I can insert a new sheet and simply type some text (and obviously no sheet/formula is refering to this new sheet) and a recalc gets triggered when obviously this should not happen.

This extract was in xlsm format. I'm not too familar with that format as I've never used office 2007, but I understand this simply indicates that it may have macros (which this sheet did not). I'm trying to locate the file in its original csv format and try importing that, but if I don't, is there some means of troubleshooting this and making excel behave properly? I don't want to turn off calculating as there are some interactive reports in this with drop-downs etc. I would just like excel to only trigger a recalc when needed, not when any cell is populated.

 

[RoryA]

It sounds to me as though you may have volatile functions (see here for details) in there somewhere (or in another open workbook).

 

[sark666]

Thank you for this. And yes I believe that is the case. I'm using some dynamic named ranges with offset, and while they are handy, I believe they are the culprit. I'm going to instead define the names via vba and avoid this issue.

I like dynamic named ranges but this has annoyed me a few times and I think I'll cease using them.

 

[RoryA]

You can also try defining them with INDEX rather than OFFSET.

 

[sark666]

Thanks again. I was just trying to store the counta part of the dynamic range is a separate cell to speed things up as per the article on volatile functions, but it didn't help much.

I've never tried this before with index but I'll give that a shot.

 

[sark666]

Not sure if there is a better method but I'm doing it as so:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

Since using ranges in VBA, I haven't bothered much with dynamic ranges. I'm kind of surprised the above works because the range is indicated to start at A2 (to exclude the header) but the index portion returns the value of the last cell, but I assume this is because it's being defined as a name and is actually returning it's location rather than it's value.

 

[RoryA]

INDEX actually returns a reference in its most common form, it's just that the way it is used is normally to return the value in that cell. The range is effectively:
=Sheet1!$A$2:A<number of populated cells in A>

 

[sark666]

yes I gather that it must be returning its address (it's reference) rather than the value, but I've never used index in that manner.

I looked here:

http://www.techonthenet.com/excel/formulas/index_function.php

And looking at the reference examples I don't see the distinction of it returning its address rather than the value.

 

[mikerickson]

INDEX returns a cell, a range object.

Usually, excel will interpret a range as its contents, but with the Range operator ( : ), it interprets that as a range.

 

[sark666]

Thank you! That makes sense. Really I should be able to see stuff like that by now. But thanks again. I'd rather it click then using it blindly.

What I was attempting to do and obviously it wasn't going to work was trying to use index as a reference seeing if I could get it to return the address rather than the value.