Find "Time" Formulas?

  • Thread starter Thread starter Ken
  • Start date Start date
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
 
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)
 
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
 
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()
 
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
 
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).
 
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
 
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)
 
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
 
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)
 
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
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Bob/Norm/Dave ... Thanks for supporting this board ... &
for helping me through a few headaches ... Hope you all
have a RAINBOW Weekend ... Kha
 
Back
Top