none
Need current date to populate cell on changing value of another cell RRS feed

  • Question

  • I have a sheet that values are entered into one cell and I need another cell to then display the current date and make it static. There are several of these cells involved and the dates that the data is entered will be different each time. CreateDate in Word would really have helped here but I can find no way to do this in Excel, at least not at the cell function area.

    I have tried "Now" and "Today" and tried to force that to a fixed text string but can't seem to master that.

    =IF(D6 = 0,"Not Paid", +TODAY())

    Office Excel 2007

    Any suggestions would be appreciated.

    Thanks


    Glenn

    Monday, February 3, 2014 11:32 AM

Answers

  • You need a bit of VBA for that, you cannot do that with a formula.

    Right-click the sheet tab and select View Code from the context menu.

    Copy the example below into the module that appears, and modify it to suit your situation.

    Let's say that if you change a cell in the range D2:D10 to 0, you want to enter either "Not Paid" or the current date in the cell next to it (in column E).

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        If Not Intersect(Range("D2:D10"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each cel In Intersect(Range("D2:D10"), Target)
                If cel.Value = 0 Then
                    cel.Offset(ColumnOffset:=1).Value = "Not Paid"
                Else
                    cel.Offset(ColumnOffset:=1).Value = Date
                End If
            Next cel
            Application.EnableEvents = True
        End If
    End Sub

    Make sure that you save the workbook as a macro-enabled workbook(.xlsm, .xlsb or .xls, but NOT .xlsx), and that you enable macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Chevyman57 Tuesday, February 4, 2014 11:15 AM
    Monday, February 3, 2014 3:08 PM

All replies

  • You need a bit of VBA for that, you cannot do that with a formula.

    Right-click the sheet tab and select View Code from the context menu.

    Copy the example below into the module that appears, and modify it to suit your situation.

    Let's say that if you change a cell in the range D2:D10 to 0, you want to enter either "Not Paid" or the current date in the cell next to it (in column E).

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cel As Range
        If Not Intersect(Range("D2:D10"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each cel In Intersect(Range("D2:D10"), Target)
                If cel.Value = 0 Then
                    cel.Offset(ColumnOffset:=1).Value = "Not Paid"
                Else
                    cel.Offset(ColumnOffset:=1).Value = Date
                End If
            Next cel
            Application.EnableEvents = True
        End If
    End Sub

    Make sure that you save the workbook as a macro-enabled workbook(.xlsm, .xlsb or .xls, but NOT .xlsx), and that you enable macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Chevyman57 Tuesday, February 4, 2014 11:15 AM
    Monday, February 3, 2014 3:08 PM
  • Hans,

    Works great.

    Thanks Again,


    Glenn

    Tuesday, February 4, 2014 11:27 AM