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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window kept popping
up even though no changes had been made to the sheet. After a lengthy
investigation I tracked to problem to the OFFSET function. I had just
replaced several LOOKUP functions with OFFSET functions: which I considered
would be faster. When I went back to the lookup functions the "Save" window
quit popping up. I did some experiments on a separate spreadsheet with the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous "Calculate"
events were being generated. For example double-clicking on a row or column
to check the height or width caused a calulation when I released my finger
after the second click (but not before). I have since created my own offset
function in visual basic which works perfectly and does not cause any
anomalous events and the "Save" pop-up does not occur, unless of course I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window kept popping
up even though no changes had been made to the sheet. After a lengthy
investigation I tracked to problem to the OFFSET function. I had just
replaced several LOOKUP functions with OFFSET functions: which I considered
would be faster. When I went back to the lookup functions the "Save" window
quit popping up. I did some experiments on a separate spreadsheet with the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous "Calculate"
events were being generated. For example double-clicking on a row or column
to check the height or width caused a calulation when I released my finger
after the second click (but not before). I have since created my own offset
function in visual basic which works perfectly and does not cause any
anomalous events and the "Save" pop-up does not occur, unless of course I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window kept
popping
up even though no changes had been made to the sheet. After a lengthy
investigation I tracked to problem to the OFFSET function. I had just
replaced several LOOKUP functions with OFFSET functions: which I
considered
would be faster. When I went back to the lookup functions the "Save"
window
quit popping up. I did some experiments on a separate spreadsheet with
the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous
"Calculate"
events were being generated. For example double-clicking on a row or
column
to check the height or width caused a calulation when I released my
finger
after the second click (but not before). I have since created my own
offset
function in visual basic which works perfectly and does not cause any
anomalous events and the "Save" pop-up does not occur, unless of course
I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window kept
popping
up even though no changes had been made to the sheet. After a lengthy
investigation I tracked to problem to the OFFSET function. I had just
replaced several LOOKUP functions with OFFSET functions: which I
considered
would be faster. When I went back to the lookup functions the "Save"
window
quit popping up. I did some experiments on a separate spreadsheet with
the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous
"Calculate"
events were being generated. For example double-clicking on a row or
column
to check the height or width caused a calulation when I released my
finger
after the second click (but not before). I have since created my own
offset
function in visual basic which works perfectly and does not cause any
anomalous events and the "Save" pop-up does not occur, unless of course
I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...el.programming

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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window
kept
popping
up even though no changes had been made to the sheet. After a
lengthy
investigation I tracked to problem to the OFFSET function. I had
just
replaced several LOOKUP functions with OFFSET functions: which I
considered
would be faster. When I went back to the lookup functions the
"Save"
window
quit popping up. I did some experiments on a separate spreadsheet
with
the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous
"Calculate"
events were being generated. For example double-clicking on a row
or
column
to check the height or width caused a calulation when I released my
finger
after the second click (but not before). I have since created my
own
offset
function in visual basic which works perfectly and does not cause
any
anomalous events and the "Save" pop-up does not occur, unless of
course
I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to
the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader
and
then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming

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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window
kept
popping
up even though no changes had been made to the sheet. After a
lengthy
investigation I tracked to problem to the OFFSET function. I had
just
replaced several LOOKUP functions with OFFSET functions: which I
considered
would be faster. When I went back to the lookup functions the
"Save"
window
quit popping up. I did some experiments on a separate spreadsheet
with
the
same results. I placed a test message in the "Calculate" event and
determined that when the OFFSET function was used, anomalous
"Calculate"
events were being generated. For example double-clicking on a row
or
column
to check the height or width caused a calulation when I released my
finger
after the second click (but not before). I have since created my
own
offset
function in visual basic which works perfectly and does not cause
any
anomalous events and the "Save" pop-up does not occur, unless of
course
I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to
the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader
and
then
click "I Agree" in the message pane.


http://www.microsoft.com/office/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming

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

mickey wrote:
While developing a spreadsheet I noticed that the "Save" window
kept
popping
up even though no changes had been made to the sheet. After a
lengthy
investigation I tracked to problem to the OFFSET function. I
had
just
replaced several LOOKUP functions with OFFSET functions: which
I
considered
would be faster. When I went back to the lookup functions the
"Save"
window
quit popping up. I did some experiments on a separate
spreadsheet
with
the
same results. I placed a test message in the "Calculate" event
and
determined that when the OFFSET function was used, anomalous
"Calculate"
events were being generated. For example double-clicking on a
row
or
column
to check the height or width caused a calulation when I
released my
finger
after the second click (but not before). I have since created
my
own
offset
function in visual basic which works perfectly and does not
cause
any
anomalous events and the "Save" pop-up does not occur, unless
of
course
I
make a change to the sheet. I'm using Excel 2003.

----------------
This post is a suggestion for Microsoft, and Microsoft responds
to
the
suggestions with the most votes. To vote for this suggestion,
click
the
"I
Agree" button in the message pane. If you do not see the
button,
follow
this
link to open the suggestion in the Microsoft Web-based
Newsreader
and
then
click "I Agree" in the message pane.



http://www.microsoft.com/office/comm...id=a9094fd5-9c
a8-4744-b1dd-14a3cab7e720&dg=microsoft.public.excel.programming