Yes, I agree with you regarding the functions you mentioned. In fact TODAY
and NOW are the ones I first looked for when I encountered the problem,
thinking that perhaps I forgot and used one some where in the Worksheet (I
don't offen use RAND).

But, alias it was the OFFSET function.

Cheers.

"Lori" wrote:

This is not strange but consistent for all volatile functions.
RAND(),TODAY(),NOW(),... need to recalculate at startup to show the
current date etc. therefore a save changes prompt appears by default
when the workbook is closed. It's probably not necessary for OFFSET and
INDIRECT to do so but placing them in the volatile category drives this
behavior, this does not mean they are unstable or there is a deep
problem. Personally, I have other worries than this.

mickey wrote:

This is what I meant by OFFSET being unstable - there is no reason why that
should happen, it could be indicative of a deeper problem, which MS should
investigate and take steps to correct.

Also I am not that adverse to UDF's, believe me I am doing a lot weirder
stuff, that were to mention on this Forum, I would elicit responces as though
I were creating a "Black Hole" that would destroy all mankind (whoop's ,
personkind - I don't want to appear politically incorrect).

However, I do appreciate all your suggestions - Thanks again.

By the way would by any chance know of a Range property which could
effectively shield a cell from automatic re-calculation (kind of the opposite
of the "Dirty" method)?

Cheers.

"Peter T" wrote:

Indeed with Offset in a formula you will get the save message on close even
if no 'apparent' changes. Personally I wouldn't substitute Offset with a
UDF, not withstanding the alternatives, just to avoid that. I suppose if for
you it's really a problem you could add the following in the thisworkbook
module -

Private Sub Workbook_Open()
ThisWorkbook.Saved = True
End Sub

and I could save a parameter (shortens long formulas).

=Element(Cref,row,1)
vs
=INDEX(Cref,row,1)
?

Regards,
Peter T

"mickey" wrote in message
...
Hi Peter,

After my bad experiences with OFFSET I was relultant to try INDEX which
seemed similar. It was so easy to create my own, and I could save a
parameter (shortens long formulas). Also I was looking to the future for
my
application, I am going to have another UDF which does some unique
conversions after calling ELEMENT. This will be easier to code with
ELEMENT.

Going back to the OFFSET issue, so there no misunderstanding about the
problem I was having: when the OFFSET function was present, I could simply
open the workbook and then close it, without touching anything, and the
SAVE
window would pop-up. This occur with just one OFFSET function, simply
changing it to LOOKUP or my own UDF stopped the SAVE window.

Cheers

"Peter T" wrote:

Doesn't this simple worksheet formula do same more efficiently

=INDEX(Cref,row,1)

I must admit I don't know why volatile functions recalc when double
clicking
a row/col border to autosize. However I think only rarely worth
replicating
a volatile function with a UDF, or better still use an alternative as in
this case and as suggested by Jon.

Typically the impact of volatile functions won't be noticed, however
need to
be aware. When using a new function it's always worth using the function
wizard and looking for the indication of Volatile, though I think there
are
one or two functions falsely not labelled as volatile. Despite being
volatile Offset is very useful for some purposes, though not this one.

Regards,
Peter T

"mickey" wrote in message
...
Peter,

Here's the code:
Function Element(ByVal CRef As Range, Row As Integer) As Variant

Element = CRef.Cells(Row, 1).Value

End Function

CRef refers to a "Named" column range (data only, no headers, if you
want
to
include the header you'd have to add 1 to the row) in the table. Row
is
simply the relative row from the top of the column. In a passed post
I
mention how my table is constructed, in which one column serves as the
unique
reference for the table. In my application the reference column has
dates.
To find a specific row I have a cell using the MATCH function that
returns
the row of interest. Once the row is determined I reference that cell
in
my
UDF to directly fetch corresponding data from any column on the same
row.

By the way I mis-interpreted your statement about the OFFSET function
being
Volatile. For "Volatile" I was reading unstable. I now understand
that
"Volatile" in MS terms means always re-calculates when any change
occurs
in
the sheet. This still doesn't explain what I was seeing, where the
OFFSET
function was causing a "Calculate Event" to occur even when no cell
changes
had occured. Sorry for the confusion on my part.

Hope you find my simple UDF useful - post any questions you may have.

Cheers.


"Peter T" wrote:

Would you post your UDF and perhaps an example of usage if not
obvious
how
to use it.

Regards,
Peter T

"mickey" wrote in message
...
Perhaps my last post regarding the UDF I wrote wasn't clear as to
the
way
it
functions. Once a target row is identified the UDF does not need
to
perform
any searches, it vectors directly to the target cell via row and
column
index
(same as OFFSET) and returns the target value. Every element in
the
target
row can be retrieved by direct vector. The UDF itself is
comprised of
one
VB
instruction: I don't think you'll find anything faster.


"Jon Peltier" wrote:

Mickey -

You said in the first place you switched from lookups to offsets
for
performance reasons. Offset gave problems with its volatility,
so
you
coded
a UDF. How does this UDF compare with the lookups? I would
expect it
to
be
slow.

Charles Williams has a lot of information about optimizing
Excel's
calculations on his web site:

http://www.decisionmodels.com/index.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"mickey" wrote in message
...
Kieranz,

I would be happy to share the code with you, however once I
decided to
code
it myself I made it somewhat unique to my application. I'll
explain
how
it
works and what it expects, and if you still feel it would be
of
use to
you
I'll provide it.

First, my spreadsheet is in the form of a table with various
columns
(so
far
nothing unique here).

Fromula Returns Specific Date

Row Number
__________________________________________________ ________
Dates | ColName2 | ColName3 | etc.
__________________________________________________ ________
Date 1 Data Data etc.
Date 2 Data Data etc.
Date 3
Date 4
etc.

Above the table is a cell which contains a formula which
returns a
specific
date, I my case the last date, but it could be any date. Just
above
the
upper lefthand corner of the table is a cell that contains the
relative
ROW
number of the specific date.

My UDF require two things: The "Name" label of the Column of
interest
(i.e.
ColName2); and the "Name" of the cell containing the row
number of
the
searched for date(in my case "RowIndex"). The routine then
returns
the
Value
of the data in "ColName2" at the row specified by RowIndex.
Because
of
the
way I provide the information the UDF is one line of VB code.

Here's what the function looks like in a cell:
=ELEMENT(ColName2,
RowIndex)

I could have done this 100 different ways, but this was very
efficient.
ColName2, and RowIndex are passed as "Range" types and my UDF
can
use
the
reference to RowIndex to calculate the absolute row address of
the
target
cell, as the reference to the target column provides the
column
information.

This can work for any table where there's a column that is the
reference
to
the rest of the table,(i.e. the reference column data should
be
unique:
dates, time, index numbers, etc.) something you can search for
to
determine a
row number. You'll note that the reference column could be in
any
position,
it need not just be the left column. However, the "RowIndex"
cell
must be
in
the row just above the table (as it's position is used by