Date Modified Based On Cell Range

[everton1878]

Hi All,

I have a spreadsheet used for tracking product. To simplify it basically i have a list of product in column a. Column b is known as my issue column. Items that don't have an issue stay blank. Items that have an issue have a "1" entered into this column. When an issue is resolved, this is then changed to a zero via manual input. What i need to do is:

In just one cell i need it to display the date that last time an issue was resolved.

So in essence i need: when a 0 is entered anywhere in column b, cell ?? needs Now() inserting into the cell.

I was hoping to do this somehow without a macro but can't think how. If anyone has any ideas either way would be fantastic.

Thanks

 

[Joe4]

Welcome to the Board!

I am afraid that this will require VBA, specifically a Worksheet_Change event procedure that is automatically triggered upon data entry (for more on Event Procedures, see: http://www.cpearson.com/excel/Events.aspx

The VBA code would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 
'   Check to see if only one cell is selected and you are in column B
    If Target.Column = 2 And Target.Count = 1 Then
'   If the selected cell is zero, update cell C1 with current date/time
        If Target.Value = 0 Then Range("C1").Value = Now()
    End If
    
End Sub

 

[everton1878]

Hi,

thanks for the reply. I've tried out ur method along with a circular referancing method I have created which also works. Now just to decide which operates best...

Thanks again.