Prev: how to delete an excel worksheet?
Next: Conditional Formatting from 2007 to 2003
From: Norm on 15 Apr 2010 11:21
I am using this formula
=IF(D5="","",NOW())
I want to stop it updating everytime I put a new entry in the worksheet
I need the time to stay static
Cheers!!
From: Mike H on 15 Apr 2010 11:31
Hi,

Unfortunately that will happen using NOW and there's no way around it using
a formula but you can use a macro. This one looks at a range in column D and
when it changes a static timestamp is put in the adjacent cell in column E.
Right click your sheet tab, view code and paste the code in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("D1:D100")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Target.Offset(, 1) = Time
Application.EnableEvents = True
On Error GoTo 0
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Norm" wrote:

> I am using this formula
> =IF(D5="","",NOW())
> I want to stop it updating everytime I put a new entry in the worksheet
> I need the time to stay static
> Cheers!!
From: Chip Pearson on 15 Apr 2010 11:52
You can do it with a circular reference. If you want the result in
cell E5, use

=IF(D5="","",IF(E5="",NOW(),E5))

You'll first need to enable iterative calculations. In Excel 2003 and
earlier, go to the Tools menu, choose Options, then the Calculation
tab. There, check the Iteration box and enter 1 for Max Iterations. In
Excel 2007 and later, click on the Office button, choose Excel
Options, then the Formulas page. There, check Enable Iterative
Calculations and set Max Iterations to 1.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Thu, 15 Apr 2010 08:21:01 -0700, Norm
<Norm(a)discussions.microsoft.com> wrote:

>I am using this formula
>=IF(D5="","",NOW())
>I want to stop it updating everytime I put a new entry in the worksheet
>I need the time to stay static
>Cheers!!
 | 
Pages: 1
Prev: how to delete an excel worksheet?
Next: Conditional Formatting from 2007 to 2003