Auto Date

[berg891832]

Is it possible that when I type data into Cell A1, the date will automatically appear next to it in cell B1? I want this down the column.

the date has to be static. I tried this =if(isempty(a1),"",today()). This works but teh date changes everyday to reflect the current date.

Bill

 

[Carnil]

Absolutely Brilliant!!!!

Thanks!

We are not worthy!

 

[VoG]

Glad it worked. I actually dashed that off in a hurry. This is better and more understandable

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Select Case Target.Column
    Case 1
        If IsEmpty(Range("U" & Target.Row).Value) Then Range("U" & Target.Row).Value = Date
    Case 2 To 5, 11 To 16
        Range("V" & Target.Row).Value = Date
    End Select
Application.EnableEvents = True
End Sub

 

[skinnea]

Hi - I'm interested to know if/how any of the code in this thread could be amended to do the following on an action tracker sheet;

In Column H there is a drop-down data validation list allowing the statuses of Ongoing, Review, Cancel or Complete (and of course null) to be attributed to a certain action.
In Column K there is a formatted calculation showing how many days are left to the action's target date that reads as follows =IF(H8="Complete","x",IF(H8="Cancel","x", IF(I8>0,(I8-TODAY()),"")))
This means that when an action is marked as Complete or Cancel, this column stops showing numbers and shows an 'x', enabling me to Sort the data in the sheet and move the Completed or Cancelled actions to the bottom of the list.
What I lose however is the date that the action was put to Complete or Cancel.

I'm therefore looking to see if I can get a static date stamp in column L based on when the data in a column H cell is set to Complete or Cancel.
This would allow me to then sort the Closed or Cancelled actions and know which ones were closed most recently.

Any ideas...?

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    Application.EnableEvents = False
    Select Case Target.Value
        Case "Cancel", "Complete": Target.Offset(0, 4).Value = Date
    End Select
    Application.EnableEvents = True
End If
End Sub

 

[arkusM]

Haha, Eight questions on the same/similar function! Good job VoG
This is almost an FAQ!!

Have filed the solutions for future ref. Thank you!

 

[nike]

Hi,

I've been searching and trying to get an answer to this date stamp, but i can't seem to get the right code or even understand how to edit the code that i see in this board. Basically, i have a Due Date (column A) and Priority (column B). The Due Date column has a formula, that when i choose the Priority level(drop down Urgent, High, Low), it calculates the Due Date from the current date and auto populates in the cell. The problem is, when i open the file the next day, and insert another Priority level, it updates everything even the previous Due Date entry. I would like for the Due Date to be static. This the formula i have in place for the Due Date:

=(TODAY())+SMALL(IF(WEEKDAY((TODAY())-1+ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0))))<6,ROW(INDIRECT("1:"&(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))+ROUND(30*((VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0))/30),0)))),(VLOOKUP((B3),{"Urgent",1;"High",14;"Low",21},2,0)))

Is there a way to make up a vb macro to do this job?

Thank You!

 

[VoG]

If you keep changing the priority level on a given row then the following won't work and I suggest that you start a new thread.

If, on the other hand, you only ever set the priority once then retain your formulas in column A and use the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
    Application.EnableEvents = False
    With Target.Offset(0, -1)
        .Value = .Value
    End With
    Application.EnableEvents = True
End If
End Sub