Record and retain the date when cell value was FIRST changed!

[Phil Payne]

Hello all

I need your expert assistance again please.

I have status columns with validated codes (ascending from 1 to 11) on Sheet1 that when selected by the user inputs the date of change of status into an adjacent cell. Over time the status changes from 1 through to 11 and on each change of status the date in the adjacent cell changes accordingly.

All that is OK but now I need to record when a particular status is reached that, when subsequently changed, will not change the status date. (I need to retain for the record when each status 9 was achieved.)

Is there a way to record and retain the date when status value was FIRST changed to Status 9?

I presently display all the status dates found on Sheet1 (up to 6026) on Sheet2. I am hoping to modify the Sheet2 formula or employ VBA to get what I need.

I hope someone can provide a solution.

Thanks very much!

 

[jim may]

Can you post your sheet2 showing your various Status and/or dates?

 

[Phil Payne]

Hello Jim,

Its very large and for ease of description I fibbed about the sheet numbers!
Sheet 1 'Data Sheet' (the source) is sheet3 of 23 and 'Sheet2' is 18 of 23 but I will try to provide cut down version asap.

Thank for the prompt response.

 

[Phil Payne]

Hello again Jim,

Sorry but it would take far too long to produce a sanitised version of my workbook. I hope images and typical formula will help.

For info the formula in cell D4 of sheet2 is typical of all cells on that sheet. "=IF(($B4='Data Sheet'!$F15)*('Data Sheet'!U15='Defects Liability Starts'!$A$1),'Data Sheet'!W15,0)"

Just found that I can't place an image in the message as its looking for an URL that I cannot provide.

Thanks for your time anyway!

Regards,

 

[jim may]

I created a new sample workbook - copy the below code into the Code Sheet (not a standard code module). This might give you some ideas.. Enter into Range F3:F6 todays Date (11/03/2014) repeatedly. As you do watch the Stat Num Code Climb up each time to a max of 9. After that you will get a Message - you cannot change further... Jim

Excel 2012
	B	C	D	E	F
1			*** LAST ***
2	Job Name	Status	Stat Num Code	Status Date	Current Change Date
3	Job 1	Mid-Point	8	10/21/2014
4	Job 2	Final	9	10/31/2014
5	Job 3	Just Started	6	9/30/2014
6	Job 4	Open	4	10/15/2014
Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    If Target.Value <> "" Then
    If Target.Offset(, -2) < 9 Then
        Target.Offset(, -2) = Target.Offset(, -2).Value + 1
        Target.Offset(, -1) = Target.Value
    Else
    MsgBox "You have previously acheived Status 9" _
           & vbNewLine & "You Cannot Change this any longer."
    End If
    End If
Application.EnableEvents = True
End Sub

 

[Phil Payne]

Thank you very much for your time Jim,

I'll get back to you tomorrow.

Cheers.

I created a new sample workbook - copy the below code into the Code Sheet (not a standard code module). This might give you some ideas.. Enter into Range F3:F6 todays Date (11/03/2014) repeatedly. As you do watch the Stat Num Code Climb up each time to a max of 9. After that you will get a Message - you cannot change further... Jim


Excel 2012

	B	C	D	E	F
Job Name	Status
Job 1	Mid-Point
Job 2	Final
Job 3	Just Started
Job 4	Open

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]*** LAST ***[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]Stat
Num
Code[/TD]
[TD="align: center"]Status
Date[/TD]
[TD="bgcolor: #FFFF00, align: center"]Current
Change
Date[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]10/21/2014[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]10/31/2014[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]9/30/2014[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]10/15/2014[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    If Target.Value <> "" Then
    If Target.Offset(, -2) < 9 Then
        Target.Offset(, -2) = Target.Offset(, -2).Value + 1
        Target.Offset(, -1) = Target.Value
    Else
    MsgBox "You have previously acheived Status 9" _
           & vbNewLine & "You Cannot Change this any longer."
    End If
    End If
Application.EnableEvents = True
End Sub

 

[Phil Payne]

Good morning Jim,

Thanks for this. Unfortunately the progression through the status' isn't linear. It is possible to go from 1 straight to 7 then 8, 9, 10 & 11.


Phil.

 

[jim may]

I've modified the Sample W/B Sheet to look like so... Also replace Code with the new code below. To test Go straight to the cells D3:D6 and enter "new/updated" Status codes, such as 5,8 (other than 9). It should allow you to change and update your Status Date with today's date. Now, enter in any cell D3:D6 - 9. Doing so should date-stamp the Column F permanently with the date your proj/job's status code was changed to a 9.

Excel 2012
	B	C	D	E	F
1			*** LAST ***
2	Job Name	Status	Stat Num Code	Status Date	Date Stat Code Chged to 9
3	Job 1	Mid-Point	8	10/21/2014
4	Job 2	Final	9	10/31/2014
5	Job 3	Just Started	5	9/30/2014
6	Job 4	Open	10	10/15/2014
Sheet1

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("D:D")) Is Nothing Then Exit Sub
Application.EnableEvents = False
    If Target.Value <> "" Then         'Deleting a Stat Num Code will have no affect
    Target.Offset(, 1) = Date
    If Target.Value = 9 Then
        Target.Offset(, 2) = Date
    MsgBox "You have acheived Status 9"
    End If
    End If
Application.EnableEvents = True
End Sub

 

[Phil Payne]

Looks promising Jim.

What if my "Target.Offset(, 2)" is a cell on Sheet2?

Thanks.

 

[jim may]

we only need to locate it (the cell on Sheet2) by perhaps using match() or some other lookup method.

What sort of table setup do you have on sheet2, that I might offer a revision to the line "Target.Offset(, 2) = Date ?

Regards,

Jim