Constant date in Excel 2K7

Hi,

You basically have two choices.

(1) You can keep the date formulae in the Date column, but when you type IC into the Status column you will have to remember to copy/pastespecial values the corresponding Date formula so that the date doesn't continue to change.
(2) Use VBA to automatically add the date into the Date column. The approach would be to use the Worksheet_Change() event handler and to put the date in the cell if the corresponding cell in the Status column contains "IC".

If you need help with option (2) then please let us know which columns are the Status and Date columns.

Sure, no problem.

Which columns (the column letters) are the Status and Dates columns?

Hi,

Define the following names in your workbook (under name manager) but change Sheet1 to the name of your worksheet.

Dates =Sheet1!$N:$N
Status =Sheet1!$M:$M


Then, right click on the sheet's tab (at the bottom of the grid) > view code. This will open up the Visual Basic Editor. Paste in the below code and you're done. If "IC" is in the Status column then the code will add the date to the Dates column; if anything else has been put in the Status column then the code will clear the Dates column.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rngDate As Range
    
    On Error GoTo ErrorHandler
    
    If Not Intersect(Target, Range("Status")) Is Nothing Then
        
        Set rngDate = Intersect(Target.Cells(1).EntireRow, Range("Dates"))
        
        Application.EnableEvents = False
        
        If CStr(Target.Cells(1).Value) = "IC" Then
            rngDate.Value = Date
        Else
            rngDate.ClearContents
        End If
        
    End If
    
ErrorExit:

    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    Resume ErrorExit
    
End Sub

Hi,

You're using Excel 2007 so on the Ribbon go to the Formulas tab and then either click on Name manager or "Name a Range" (this might also be called "Define Name" - I only have XL 2003 at work so I can't check) to get the named range set up. Have a read through this for further information - names are a very, very useful tool.

http://office.microsoft.com/en-us/ex...010147120.aspx