I need help affecting the Values of one cell by entering data into another...

[HoundofCullin]

Ok... I'm helping a friend of mine (he works at a gravelpit at the scale
house) get something set up on Excel where he enters the truck's number
(or any sort of data) in one cell and then one cell over it
automatically logs in the current time.

Does anyone have a way to help?
Does anyone even understand what I mean?
Please help... you will have a friend for life... Josh

 

[Max]

Came across an "approximately equivalent" non-macro way*
to effect the date-time stamping by Biff (below)
while thumbing through Google archives
*using data validation and NOW()

IMO, it's worth a try..

Note: Biff explained - calculation mode must be set to Automatic

----------------- begin --------------------------------------------------
From: Biff ([email protected])
Subject: now()
Newsgroups: microsoft.public.excel.worksheet.functions
Date: 2003-06-03 20:07:55 PST

Hi Eva,

Here's a method that uses the NOW() funtion if you don't
want to use any VBA. It uses data validation.

Pick an out of the way cell, say AA1, and enter =NOW().
In col B, select the range of cells in which you want the
time to appear. Format this range as to the way you want
the date/time to display. With this range still selected,
goto DATA>VALIDATION. Select ALLOW and click on LIST. In
the SOURCE box enter =AA1. This will create a dropdown
with the current date/time as the only available
selection.

Because NOW() is a volatile function, it will update every
time the sheet recalculates. However, the date/time
displayed in col B is constant because it's referencing
the value of cell AA1, not the formula.

So whenever you type data in A1 and then hit enter, cell
AA1 with the NOW() funtion will update. Then you would
select cell B1, click the dropdown arrow and click the
date/time. It's kind of 'clunky' but it serves me well. I
use this method often but with the =TODAY() function.

Biff

-----Original Message-----
I'm trying to create a worksheet where each new record
entered will have the time attached when it was entered.
So column A would have the data I enter, column B would
have the time when it was entered. I tried using the
formula =IF(A3<>0,NOW()), but for some strange reason it
changes the time of EVERY preceding record to the current
time, even if it had a different time before. I tried =IF
(A3<>0,Value(NOW())) to "freeze" the value and not change
it next time I enter a new record, but it didn't work.
Does anybody have an idea how to fix it?

Thanks,
Eva Erickson

---------------- end -----------------------

hth
Max

 

[J.E. McGimpsey]

One way:

Put this in the worksheet code module (right-click on the worksheet
tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target(1)
If .Column = 1 Then
With .Offset(0, 1)
.Value = Time
.NumberFormat = "hh:mm"
End With
End If
End With
End Sub

Now whenever an entry is made in column A the time will be entered
in the corresponding cell in column B. Modify the .NumberFormat to
suit.

 

[gocush]

If you want to do this with a macro, the following will work. It needs
to be put into the Sheet-Event section of the Visual Basic Editor.

Assumptions:

-you are listing the truck numbers in range A1 to A100
-you want the time in Col B

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A1", "A100")) Is Nothing Then
If Target <> "" Then
Target.Offset(0, 1) = Time
Else
Target.Offset(0, 1) = ""
End If
End If
Application.EnableEvents = True
End Sub

 

[Stephen Bullen]

Hi HoundofCullin,

Ok... I'm helping a friend of mine (he works at a gravelpit at the scale
house) get something set up on Excel where he enters the truck's number
(or any sort of data) in one cell and then one cell over it
automatically logs in the current time.

If you don't want to go the macro route, it may be worth mentioning that
pressing shift+ctrl+; will put the current time in a cell.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

 

[HoundofCullin]

Thanks for the quick responses you guys (I am used to waiting 1week t
indefinately for a reply in other forums) It does seem that my proble
is solved. Thanks again!
Jos