Automatic date update in a cell when another cell's value changes (as calculated

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

vinwin06

Thread Starter
Joined
Jul 28, 2010
Messages
413
Hi All,

I need an help one excel formula ,i have specific range with prices, but when ever there is a change in value in the range then one particular need to be updated with now() formula. Find below the snap shot of desired data....

Product Changes happened Jan'13 Feb'13 Mar'13 Apr'13 May'13 Jun'13 Jul'13 Sep'13
Apple
Orange
Pineapple
Fruits


In the above data i have values for all product in all months. if there is any change happened then in the changes happened column need to be updated with now() formula.

Thanks in advance...

Vinwin
 
Joined
Feb 21, 2013
Messages
520
Hi

I suspect that you will need a macro to accomplish this. If you can upload a sample file I can build a basic macro to accomplish what you need.
 
Joined
Feb 21, 2013
Messages
520
So you want to record the date a change was made in a row to be recorded in column B?
Do you want to record the date of the last change or have the dates of all changes kept in the cell?
If you make multiple changes to a row at the same time do you want to record each change?
As an aside, what is the benefit of recording when a change has taken place without also knowing what the change was?

Can you send a file with examples of how you would want it to work i.e. with cells populated and a mock up data in the date cell assuming multiple changes?
 

vinwin06

Thread Starter
Joined
Jul 28, 2010
Messages
413
hi thanks for you reply. If there is change in any product in column b, it need to entered the current date and time. but I am interested to see how we can show the changes made in cell.
 
Joined
Feb 21, 2013
Messages
520
Ok...well, you really didn't answer my questions. I can't give any specific help without more detail. In General you would use a Change(ByVal Target As Excel.Range) macro. Here is an example of a change in any cell within a range that will record date and user. See if you can adapt it to your requirements.


Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'
    If Target.Column = 4 And Target.Row > 1 Then    'And Target.Cells.Count = 1  restricts changes to col D from row 2 onwards
      For i = 1 To Target.Count
        If Target(i) <> "" Then   ' if you double click in a cell but do not add data the date an user name will not be added
            Target.Offset(0, 5) = Date + Time  'puts the date aand time in Col I
            Target.Offset(0, 6) = Environ("username")
        End If
       Next
    End If
'
 End Sub
 

vinwin06

Thread Starter
Joined
Jul 28, 2010
Messages
413
Hi Xcubed,

Find attached the excel file with some data in it.

So you want to record the date a change was made in a row to be recorded in column B?
yes your correct.
Do you want to record the date of the last change or have the dates of all changes kept in the cell?
I want to keep the date of last change date. Or else how we can track all the changes.
If you make multiple changes to a row at the same time do you want to record each change?
No as of now. But how we can keep track of all changes at the same time.(My personal question)
As an aside, what is the benefit of recording when a change has taken place without also knowing what the change was?
Yeah good point but i want to know how we can track that?
 

Attachments

Joined
Feb 21, 2013
Messages
520
this solution will track all changes in a separate sheet called Audit. You can sort the resulting table to view changes to any one cell

But you should consider using the Track Changes option which is, I think, your best solution for your needs.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top