1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by vinwin06, Jun 1, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. vinwin06

    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
     
  2. XCubed

    XCubed

    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.
     
  3. vinwin06

    vinwin06 Thread Starter

    Joined:
    Jul 28, 2010
    Messages:
    413
    find attached the sample file for your reference....
     

    Attached Files:

  4. XCubed

    XCubed

    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?
     
  5. vinwin06

    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.
     
  6. XCubed

    XCubed

    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
    
     
  7. vinwin06

    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?
     

    Attached Files:

  8. XCubed

    XCubed

    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.
     

    Attached Files:

  9. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1100162

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice