Create a formula to calculate when info. was last updated

A

anne7134

Hi all:

I'm not sure if this is the appropriate forum, but I'd creating a file that
includes multiple lines and I'd like a cell for each line to act as a status
cell and note the last update to that line. Is there a way to do that?
 
A

anne7134

Hello Dreid,

I wish it was, but the request is that I have a status column for each row
that would change depending on the last update to that row. I assumed that I
could use some sort of conditional formatting to note an update (maybe the
status column could turn red if the row was updated on the last save), but I
don't know how to create a cell/macro/formula that I can run the conditional
formatting off of.

Thanks so much,
Anne
 
A

anne7134

I hate to reply to my own post, but I went ahead and set it up with track
changes, and although it's not exactly what they wanted, I actually think it
may be more useful! I'd still like to add in the status update column, so if
you have an idea, that would be great, but this is good.

Thanks so much.
 
D

Dreid

Sounds complicated !

I would use a workaround and probably operate with three worksheets; one for
todays data,one for yesterdays data and a third containing an @if statement
which says " If sheet 1 a1 is equal to sheet 2 a1 then "No change" otherwise
" Changed Data".

You could use conditional formatting on this. I guess this maybe too
simplistic though and your requirement prevents the use of more than one sheet

Good luck

David
 
G

Gord Dibben

anne

You can use event code in the worksheet to track when a cell is altered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
module.

As written, when a value is typed into any cell in column A, the adjacent cell
in Column B gets a time stamp.

Other configurations are available depending upon your layout.


Gord Dibben MS Excel MVP
 
Top