I need a formula

I need a formula in column B to look at column A's data. If the corresponding cell in Column A is changed in any way, it replaces the date that is in the Cell in Column B. Can this be done with a formula or is a macro going to be necessary?

See the attached for an example.
10-13-11-Date-Change.xlsx
LVL 1
wrt1meaAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
wrt1mea,

Please see attached. The macro only looks at cells in the sheet's UsedRange (otherwise something like deleting a column may have dramatic effects!).

Code is...
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xTarget As Range
Dim xCell As Range
Dim xChange As Range

Set xTarget = Intersect(Target, Union(Range("D:D"), Range("F:F")), ActiveSheet.UsedRange)
If xTarget Is Nothing Then Exit Sub

Application.EnableEvents = False
    For Each xCell In xTarget
        If xChange Is Nothing Then
            Set xChange = Cells(xCell.Row, 2)
        Else
            Set xChange = Union(xChange, Cells(xCell.Row, 2))
        End If
        xChange = Format(Now(), "mm/dd/yyyy")
    Next
Application.EnableEvents = True

End Sub

Open in new window


Regards,
Brian. 10-13-11-Date-Change-V2.xlsm
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Hi, I'm thinking that a formula in column B 'watching' a cell in column A waiting for it to change will create a circular reference. So you will need to write a macro for this.
0
 
pony10usCommented:
I placed an additional row in D with this formula:  =DATE(YEAR($B$1), MONTH($B$1), DAY($B$1)+A1-$A$1)

Probably not the best method though. And it will change the month when necessary.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
byundtCommented:
You can manually time-stamp a cell by holding the Control and Shift keys down and hitting the colon key (CTRL Shift :). You can manually date stamp a cell by holding the Control key down and hitting the semicolon ; (CTRL ;). The results will be formatted using the default short time and date format for your locale.

If you want the time-stamping to be automatic, by far the best approach uses VBA code. Here is a macro that must be installed in the code pane for the worksheet being watched. Since a lot of people want to time stamp more than one cell, I generalized the code to put the time stamp in column B next to the cell in column A that was changed. If column A is blanked, then so too is column B. The code is restricted to watching cells A1:A10, but that is easily changed in the place indicated. Note that you'll need to format the cells that will be time-stamped to display the time/date as you wish.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, cel As Range
Set targ = Intersect([A1:A10], Target)         'Change the reference to A1:A10 to the actual range you want to watch for changes
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cel In targ.Cells
    If cel = "" Then
        cel.Offset(0, 1).ClearContents
    Else
        cel.Offset(0, 1) = Now()
    End If
Next
Application.EnableEvents = True
End Sub


Another approach puts the time-stamp in your cell when you double-click it. This sub also goes in the worksheet code pane.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim targ As Range, cel As Range
Set targ = Intersect([B1:B10], Target)      'Change the reference to B1:B10 to the actual range you want to time-stamp
If targ Is Nothing Then Exit Sub

For Each cel In targ.Cells
    cel = Now()
Next
Cancel = True
End Sub


I don't recommend it at all--but if you simply must have a formula for this purpose (rather than the recommended VBA code), then you'll need to turn on iterations (using the Tools...Options...Calculation menu item) with a maximum of 1 iteration. So too will anyone you give the workbook to--they'll get a circular reference error message if they don't. You would then use a formula like:
=IF(A2="","",IF(B2="",NOW(),B2))           formula that time-stamps B2 when cell A2 has data entered
0
 
byundtCommented:
If you use the macro (as I strongly recommend), you will need to format column B with the desired date format.
0
 
wrt1meaAuthor Commented:
Byundt..Thanks for the strong hint as I am going the way of the macro. How could I include two columns to watch for changes? If I wanted to watch Column D or Column F, then it Date/Time stamps Column !?
0
 
lohiCommented:
Hi
We need to write macro then only we can see answer other column.
0
 
byundtConnect With a Mentor Commented:
The code below watches cells D1:D10 and F1:F10 for user changes in those cells. If any occur, it date stamps column I in the same row. The date stamp is removed if a cell in column D or F is cleared.  This macro must be placed in the worksheet code pane, and won't work at all if you put it anywhere else.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim targ As Range, cel As Range
Set targ = Intersect(Union(Range("D1:D10"), Range("F1:F10")), Target)        'Change the reference to D1:D10 and F1:F10 to your desired ranges

Application.EnableEvents = False
For Each cel In targ.Cells
    If cel = "" Then
        cel.EntireRow.Range("I1").ClearContents         'Clear the date in column I, same row
    Else
        cel.EntireRow.Range("I1") = Date                     'Add a date in column I, same row
    End If
Next
Application.EnableEvents = True
End Sub

Open in new window

0
 
wrt1meaAuthor Commented:
Works Great!
0
 
redmondbCommented:
Thanks, wrt1mea!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.