How to keep NOW() and TODAY() From updating.

[NYRANGERS423]

So, I need to keep the NOW() and TODAY() from changing every time I change a cell or open the spreadsheet. I need the columns C, (TODAY()), F and G (NOW()) to stop updating once there is a time or date in the column. Thank you for the help! If you need anymore information Let me know.

 

[Rick Rothstein]

I got it to work correctly with the given code. Thank you for your help.

Great! Glad to hear it. And you are quite welcome.

 

[NYRANGERS423]

Okay, assuming values are only entered one-at-a-time into Columns A and B, see if this event code does what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Not Intersect(Target, Columns("A")) Is Nothing Then
      If Target.Row > 1 And Target.Offset(, 2) = "" Then
        Target.Offset(, 2) = Date
        Target.Offset(, 5) = Time
      End If
    ElseIf Not Intersect(Target, Columns("B")) Is Nothing Then
      If Target.Row > 1 And Target.Offset(, 5) = "" Then Target.Offset(, 5) = Time
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the above code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Great! Glad to hear it. And you are quite welcome.

Wait! One last thing. Please and thanks so much, How can i modify the code just a little bit to where in the column of ID Returned or Column J I can also have the contents of that not change. I experimented for example and added the code Target.Offset(, 9) = "TEST" underneath Target.Offset(, 5) = Time after the elseIf. The cell then added TEST to the cell, and the cell did not change when I deleted whatever was in A2 or B2. Which is what I would like to happen to column J.

Essentially I am asking the same thing but instead of numbers and times to use a word. But the problem in contents i want that cell to be is on another sheet and I don't know how to bring that information from one sheet to another.

1) If Target.Row > 1 And Target.Offset(, 5) = "" Then
target.offset(, 9) = (cell D2 from another sheet named "Name List")

How can I accomplish this. Thanks a ton again.

 

[Rick Rothstein]

Wait! One last thing. Please and thanks so much, How can i modify the code just a little bit to where in the column of ID Returned or Column J I can also have the contents of that not change.

That kind of depends on how it is getting its values, so, how/when does Column J get its values?

 

[NYRANGERS423]

That kind of depends on how it is getting its values, so, how/when does Column J get its values?

OK so how will be when who ever is using the spreadsheet at that time will write there name in the cell D2 in the other sheet named Name List. Now J gets its value the same time when the End Time gets its value.

 

[Rick Rothstein]

OK so how will be when who ever is using the spreadsheet at that time will write there name in the cell D2 in the other sheet named Name List. Now J gets its value the same time when the End Time gets its value.

How? Does the user type a value in Column J or is there a formula in Column J? If there is a formula, show it to us.

 

[NYRANGERS423]

How? Does the user type a value in Column J or is there a formula in Column J? If there is a formula, show it to us.

No formula In j its just a cell that will have the users name.

 

[NYRANGERS423]

No formula In j its just a cell that will have the users name.

I think i answered that wrong. The column j or Returned ID gets its value when the time end gets its timestamp. The value of J needs to come from the other sheet named 'Name List' and the value or name of the person working at the time will be in cell D2.

 

[Rick Rothstein]

I think i answered that wrong. The column j or Returned ID gets its value when the time end gets its timestamp. The value of J needs to come from the other sheet named 'Name List' and the value or name of the person working at the time will be in cell D2.

So Column J is filled in by code then?

 

[NYRANGERS423]

So Column J is filled in by code then?

yes it is this =IF(B2="","", 'Name List'!$D$2) How will I get the cell not to change when the new worker for a shift changes the name on the other sheet to a different name

 

[Rick Rothstein]

yes it is this =IF(B2="","", 'Name List'!$D$2) How will I get the cell not to change when the new worker for a shift changes the name on the other sheet to a different name

Uh, that is not code (what I posted in Message #11 is code)... that is a formula. You told me Column J did not have formulas in it (Message #26), although you may have thought you were correcting that in the next message. Anyway, so you are saying cell J2 has this in it....

=IF(B2="","",'Name List'!$D$2)

correct? I see you have an absolute cell reference for $D$2... what formula is in cell J3 then?