Not sure why you regard Offset as unstable!

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)?

Nothing I know of other than setting calc to manual. Can't imagine how
anything like what you describe would work.

FWIW, I have noticed sometimes wb's with volatile functions don't re-calc on
file open even with calc set to automatic. The tell-tale is "Calculate" in
the status bar.

As for creating a "Black Hole" nobody's accused you of that. UDF's are a
valuable resource and I'm quite sure yours would do no harm. If you got the
impression of being jumped on earlier on it may have been triggered by what
appeared to be an unqualified yet dogmatic view that your UDF was a superior
alternative to Excel's problematic worksheet function :-)

Regards,
Peter T



"mickey" wrote in message
...
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
ELEMENT,
combined
with it's contents to determine the absolute cell address
in
the
table),
in
my case it's also in a hidden row.


If this works for you I'll provide you the code.

:-)





"Kieranz" wrote:

Hi Mickey
Great to know this. I suffered and gave up.
You mentioned "I have since created my own offset
function in
visual
basic which works perfectly", I wondered if this could be
made
available to us on this UG.
Many thks
Rgds
KZ