formula question

[Mike]

I have a spreadsheet that brings in live stock data. Is
there anyway that I can create a formula (not a macro)
that will tell me the last time a particular cell changed?

thanks in advance.

Mike

 

[Paul Corrado]

This may work

=NOW()+"price cell"-"price cell"

When the worksheet is set to automatic recalculation this formula will
update every time the dude value changes. Of course it will update when a
number of other things happen, but I think it is a good as you will get
without a macro.


PC

 

[Paul Corrado]

Damn spell check..

dde not "dude" links.

This may work

=NOW()+"price cell"-"price cell"

When the worksheet is set to automatic recalculation this formula will
update every time the dude value changes. Of course it will update when a
number of other things happen, but I think it is a good as you will get
without a macro.


PC

 

[Norman Harker]

Hi Paul!

I thought you were right first time. After all it was livestock data.
<vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Wednesday 16th July: Argentina (Independence
Day); Brazil (Sao Paulo State Civil Holiday); Isle of Man (Senior Race
Day); Morocco (King Hassan II’s Birthday); Palau (Constitution Day).
Celebration: Baha’i (Martyrdom of the Bab).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[mike]

Paul,

thanks for the response. That doesn't work. The time
appears every time a recalc takes place. I only want the
time to update/appear when a particular cell changes.
Perhaps there is no way to do this wihtout a macro?

 

[Paul Corrado]

Guess the spellchecker does know what its doing. Who would have thought.

PC

 

[Norman Harker]

Hi Mike!

Charles Williams is the Excel performance wizard and a visit to his
site might help you:

Charles Williams
http://www.decisionmodels.com/calcsecretsj.htm

However, the way I see it is that Now() is volatile and that this
causes recalculation even if the precedents haven't changed. With a
recalculation of the volatile function cell all of the dependencies
will also recalculate.

It may well be that using an even handling subroutine might be
quicker. It might cut in every time there's a recalculation but then
so does NOW. But it would make no change if your dependencies haven't
changed and would then end.

But I stress that this hasn't been checked. It just seem that it might
be quicker.

If recalculation is getting to be a pain during construction phase, as
opposed to during user operation, you can always toggle auto and
manual re-calculation modes. Some delay in calculation is often not as
unacceptable as you might think. (Again, see Charles' comments on this
point).

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Thursday 17th July: (e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.