Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Row/Cell timestamp in Excel 2010

Posted on 2012-08-28
4
Medium Priority
?
1,464 Views
Last Modified: 2012-08-28
I have a spreadsheet that a requirement is to Date/Timestamp (C2) a cell based on when the proceeding Cell (B2) is updated to the stored item “NMC”. The formula that I am using is as follows:
=IF(B2="NMC",IF(B2="NMC",NOW(),B2),"")
However, this formula works…too well… not only does that ROW timestamps, but all the other Rows with the same record changes as well. Or if I select any of the other two items (PMC or FMC) the timestamp will change immediately for any record with “NMC”.

I am a loss to how to fix this timestamp for the one record, not the others unless they are physically changed.

see the attached file
0
Comment
Question by:edrz01
  • 2
4 Comments
 
LVL 35

Expert Comment

by:Norie
ID: 38342783
How is the value in B2 updated?
0
 

Author Comment

by:edrz01
ID: 38342808
B2 is a pull-down with three selection "NMC", "PMC", "FMC"
It is selected by the user. I only want the "NMC" to timestamp, if either of the other two are selected...blank the timestamp in C.

Currently there are 94 records, if the user deems to change the status to NMC then datestamp, else NULL the timestamp.

Changing one row/record should not change any of the times in the other rows.
0
 
LVL 10

Accepted Solution

by:
tdlewis earned 2000 total points
ID: 38342907
Add this subroutine to the Worksheet in the Visual Basic Editor:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.value = "NMC" Then
        Target.Cells(1, 2).value = Now()
    ElseIf Target.value = "FMC" Or Target.value = "PMC" Then
        Target.Cells(1, 2).value = ""
    End If
End Sub

Open in new window

0
 

Author Closing Comment

by:edrz01
ID: 38342933
Thank you very much tdlewis..!!! That's why you guys are the experts!! Perfect solution...!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question