In message of Mon, 30 Jul 2012
09:12:13 in microsoft.public.excel.programming, Walter Briscoe
writes
In message of Sun, 29 Jul 2012
10:49:05 in microsoft.public.excel.programming, Walter Briscoe
writes
In message of Sat, 28 Jul 2012 09:35:05 in
microsoft.public.excel.programming, joeu2004 writes
"Walter Briscoe" wrote:
I have a .xls file, opened by Excel 2003.
When I open it, either by clicking it, or from a shortcut,
or by opening it from Excel, or even in safe mode, it is
changed and I get "Do you want to save the changes you made
to 'X.xls'?" I can open other files without changing them.

This can happen when opening files in Automatic calculation mode that
contain formulas that reference "volatile" functions.

Some obvious "volatile" functions that do change with each
recalculation are RAND, RANDBETWEEN, TODAY and NOW. Presumably you
don't use them, because I infer that you do not expect the file to
actually change.

Some less-obvious "volatile" functions are OFFSET and INDIRECT.

[snip]

Thanks for a helpful reply.
I thought about volatile functions after posting.
I had considered NOW, but would not have thought of INDIRECT.
I trimmed my workbook to a single example "constant" formula
=INDIRECT("Sheet1!J"&201)

In the real file that is =INDIRECT("Sheet1!J"&$A2) where A2 is
=MATCH($B$2,Sheet1!$A:$A,0)

I can write some code to evaluate the formulae when required.
Any other ideas?

Sorry to add a second reply. I have 17 sheets. In each, INDIRECT is used
13 times in a subrow.
I had used B2 in each sheet to do MATCH and avoid repetition of the
calculation.

I NOW do
D2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,4)
E2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,5)
...
P2 as =VLOOKUP($B$2,Sheet1!$A$1:$Z$512,16)

In that,
1) I don't much like Sheet1!$A$1:$Z$512.
I suppose I could use a named range for it.
Is there a way to specify the used range in a worksheet?

2) I have 4 in my formula in cells(2,4), 5 in cells(2,5), ...
16 in cells(2,16).
Is there any way to use the fact that the numbers match?

Indeed, there is! COLUMN() and ROW() have the useful feature of
accessing the current cell, when given no argument. I have a named range
called Stations and all the relevant cells are set to
"=VLOOKUP($B$2,Stations,COLUMN())".



My file now opens without changing itself and seems to open much faster.
Sadly, I had not measured and did not save the file before change.

--
Walter Briscoe