Find "Time" Formulas?

K

Ken

Excel 2000 ... My spread sheets with "Time" Formulas such
as "Today()" ... automatically update when opened ... &
ask if you wish to save chgs when closed if you make no
other changes ... This I understand ... However, is there
a way to find the cells that contain the "Time" formulas
that are automatically updating & causing this "SAVE"
message???

Thanks ... Kha
 
B

Bob Phillips

Ken,

Just do a Find on TODAY

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

Ken

Bob ... (Hi)

Yes ... I understand use of "Find" ... However, aren't
there other "Time" functions that will cause a sheet to
automatically update other than the "Today()" function?

The issue is ... I do not know what these other functions
might be ... Please advise ... Thanks ... Kha
 
D

Dave Peterson

They're not all time related:

Do you have any of these volatile functions in your workbook?

AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()
 
B

Bob Phillips

But Dave, how many are likely to change if as the OP says, '... if you make
no other changes ' other than TODAY(), NOW() or RAND()?

Bob
 
J

JE McGimpsey

If any volatile functions exist in the workbook, the file will be
"dirty" when closed and you'll get the dialog. Doesn't matter if the
values change (At least for all Mac versions).
 
K

Ken

Ok ALL ... I am still listening

Above said ... I will pose the question again ... Is there
a way to collectively locate Volatile Functions within a
spread sheet ... or ... must one 1st know all Volatile
Functions & search them out "one by one" using FIND?

Please advise & Thanks for all the support ... Kha
 
B

Bob Phillips

Ken,

Dave gave you a good list.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
K

Ken

Dave/Bob ... Thanks for sticking with me on this ...
OK ... Following is a cut/paste from the article ref in
Dave's response.

Functions that are recalculated automatically when data in
the worksheet changes are called volatile functions. The
following functions are volatile:

AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()

Above said ... Does this mean that a WorkBook containing
any of the above Functions will ask "Do you want to SAVE
Chgs?" if all you do is open the WorkBook ... make NO
chgs ... then attempt Close of WorkBook? ... Thanks ... Kha
 
B

Bob Phillips

Ken,

Yes, I think that is the case. I just ran a simple test with OFFSET and it
seemed to confirm that this is the case.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
N

Norman Harker

Hi Ken!

Extract from Charles Williams' website:
Quote >>

Some of Excel's functions are obviously volatile: RAND(), NOW(),
TODAY()

Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(),
INFO()

(although the CELL("Filename") function is not volatile)

Some are volatile in some versions of Excel but not in others: INDEX()
became non-volatile in Excel 97.

A number of functions that are documented by Microsoft as volatile do
not actually seem to be volatile when tested:

INDEX(), ROWS(), COLUMNS(), AREAS()

<< End Quote

Me thinks that Microsoft speaks with forked tongue!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
G

Guest

Bob/Norm/Dave ... Thanks for supporting this board ... &
for helping me through a few headaches ... Hope you all
have a RAINBOW Weekend ... Kha
 
Top