Prev: Vlookup and combine all references
Next: help with if & order ..??
From: FS on 25 Mar 2010 16:31
I creating a basic checklist in an Excel spread sheet and need advice on how
to fill a static date into the adjacent cell. e.g. when I type any caracter
into A2, I need the current date so fill in automatically into AB. The date
must not reset each time I open the work sheet. So far, I got this formula.
=IF(D7="?", NOW(), "")
However, I don't know what the ? mar should be in order to fill the date
into the adjacent cell by entering any character.
Will appreciate your support.
Thanks, FS.
From: Mike H on 25 Mar 2010 16:53
Hi,

This question; well to me at least, is confusing. You refer to A2 changing
and putting a date in AB and in your example formula you refer to d7.

To enter a static date automatically you need VB. This will put the
date/time in the adjacent cell if any cell in column A is changed. 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("A:A")) Is Nothing Then
Application.EnableEvents = False

Target.Offset(, 1) = Now

Application.EnableEvents = True
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.


"FS" wrote:

> I creating a basic checklist in an Excel spread sheet and need advice on how
> to fill a static date into the adjacent cell. e.g. when I type any caracter
> into A2, I need the current date so fill in automatically into AB. The date
> must not reset each time I open the work sheet. So far, I got this formula.
> =IF(D7="?", NOW(), "")
> However, I don't know what the ? mar should be in order to fill the date
> into the adjacent cell by entering any character.
> Will appreciate your support.
> Thanks, FS.
From: Gord Dibben on 25 Mar 2010 17:27
NOW() is a volatile function and will update whenever calculation takes
place.

There are a couple of methods to create a static date pointed out on John
McGimpsey's site.

http://www.mcgimpsey.com/excel/timestamp.html

One uses circular references and worksheet function NOW()

Other uses VBA


Gord Dibben MS Excel MVP

On Thu, 25 Mar 2010 13:31:01 -0700, FS <FS(a)discussions.microsoft.com> wrote:

>I creating a basic checklist in an Excel spread sheet and need advice on how
>to fill a static date into the adjacent cell. e.g. when I type any caracter
>into A2, I need the current date so fill in automatically into AB. The date
>must not reset each time I open the work sheet. So far, I got this formula.
>=IF(D7="?", NOW(), "")
>However, I don't know what the ? mar should be in order to fill the date
>into the adjacent cell by entering any character.
>Will appreciate your support.
>Thanks, FS.

 | 
Pages: 1
Prev: Vlookup and combine all references
Next: help with if & order ..??