Hello,
I am trying to find the solution to the following problem;
If someone changes the status in column K to "Engineer to Review" i would like to have the value in column P show as the date it was changed, ie "today()"
I have managed to make it work like this:
=IF($K2:$K150<>"Engineer to Review","",IF($K2:$K150="Engineer to Review",(TODAY())))
Similarly, if column K is changed to "Uploaded to Server and Alarm" i would like todays date value entered into column Q however, column P must remain unchanged, ie the date it was originally entered.
a single solution to the above on its own would be great yet i have a second question.... IF at all possible it would also be useful for column K to change to either "Engineer to Review" or Uploaded to Server and Alarm" depending on a non-blank entry into column P or Q.
a combination of all of the above would be ideal although i am pretty sure it would involve circular references and i dont have the foggiest clue how to use those.
Any help with one or the other or both would be greatly appreciated!
Regarding circular references: you don't want to "use them" (I don't think)! Someone jump in and correct me here, but they can't be "resolved," therefore are of no use!
I think you need to add another column to do this, and not create a circular reference.
1.
if a date is entered into "With Engineer for review" column P, the status in column K will change to "engineer to review"
if column K is changed to "Engineer to review", todays date is entered into column P
2.
if a date is entered into "Uploaded to Alarm" column Q, the status will change in column K to "Uploaded to Server and Alarm"
if column K is changed to "Uploaded to Server and Alarm", todays date is entered into column Q but column P remains unchanged.
A couple of questions here:
Can any other values be entered in column K?
If "XYZ" is entered in Column K and then "With Engineer for review" a second time should column P be changed to the new date?
There are several other possible entries for column K, they are in stages shown in sequencial order below
ENTER EXAM STATUS
Cancelled - See Comments
Possession Req'd - See Comments
Notes on Server - Unassigned
Report held by Engineer
Report held by Author
Engineer to Review
Uploaded to Server & Alarm
Unknown
Once "engineer to review" has been chosen, the only thing it would change to afterwards would be, "Uploaded to Server", which is the final stage.
Yes, but users being what they are will "play" with the contents or change their minds and flip-flop back and forth. 35 years experience speaks.
So do you need to prevent that from happening?
i suspect you are probably right. i dont think it matters a great deal whether this is prevented or not, as long as a "for review" remains when it is then changed to "Uploaded"
I don't think it is possible using a formula but I have created a macro that will do everything you've
indicated you want. I've not fully tested it so do your due dilligence and test it.
This macro should go on the worksheet macro tab for the worksheet you have above.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
Select Case Target.Column
Case 11 ' "K"
Select Case Target.Row
Case 2 To 150
If Target.Value = "Engineer to Review" Then
If IsEmpty(Target.Offset(0, 5)) Then
Application.EnableEvents = False
Target.Offset(0, 5).Value = Now()
Application.EnableEvents = True
End If
ElseIf Target.Value = "Uploaded to Server & Alarm" Then
If IsEmpty(Target.Offset(0, 6)) Then
Application.EnableEvents = False
Target.Offset(0, 6).Value = Now()
Application.EnableEvents = True
End If
End If
End Select
Case 16 ' "P"
Select Case Target.Row
Case 2 To 150
If IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, -5).Value = "Engineer to Review"
Application.EnableEvents = True
End If
End Select
Case 17 ' "Q"
Select Case Target.Row
Case 2 To 150
If IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, -6).Value = "Uploaded to Server & Alarm"
Application.EnableEvents = True
End If
End Select
Case Else
Exit Sub
End Select
End Sub
Also, since the code depends on specific values entered you should put the values in a list for column K
and use VALIDATION to make sure that the correct values are entered as you have indicated.
Thank you very much indeed for your time in creating this macro, it works beautifully all on its own however I currently have the following coding running, and i cant seem to get them both to work alongside each other.
sorry to be a pest, could you possibly show me how i can get them both to work, preferably by means of cut and paste ?
i am so close to finishing this spreadsheet, i would be forever in your debt if this last stage in the process can work!
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Value
Case "" 'IF BLANK'
Target.Interior.ColorIndex = xlNone 'BLANK'
I agree add it to the bottom, but for ease of reading and maintenance create some constants for the colour names and use then insttead of the numbers and localised notes ( just a thought)
also you repeat a number of parts text=black why not do this once at the head and allow the code to change it as needed, just easier to read.
I can get the new code to work without errors but i lose the colour formatting from my old code. Incidentals, i don't know how to do what you suggested, my version works so im not gonna play with it
Just need to get them both to work together :/
If Target.Rows.Count > 1 Then Exit Sub
Select Case Target.Column
Case 11 ' "K"
Select Case Target.Row
Case 2 To 150
If Target.Value = "Engineer to Review" Then
If IsEmpty(Target.Offset(0, 5)) Then
Application.EnableEvents = False
Target.Offset(0, 5).Value = Now()
Application.EnableEvents = True
End If
ElseIf Target.Value = "Uploaded to Server & Alarm" Then
If IsEmpty(Target.Offset(0, 6)) Then
Application.EnableEvents = False
Target.Offset(0, 6).Value = Now()
Application.EnableEvents = True
End If
End If
End Select
Case 16 ' "P"
Select Case Target.Row
Case 2 To 150
If IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, -5).Value = "Engineer to Review"
Application.EnableEvents = True
End If
End Select
Case 17 ' "Q"
Select Case Target.Row
Case 2 To 150
If IsDate(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, -6).Value = "Uploaded to Server & Alarm"
Application.EnableEvents = True
End If
End Select
Select Case Target.Value
Case "" 'IF BLANK'
Target.Interior.ColorIndex = xlNone 'BLANK'
Sometimes our exams get cancelled or we are unable to finish, therefore the status would be changed to "cancelled" or "possession required". If either of these are chosen i have used conditional formatting to highlight the row with either red or orange. ideally if either of these are chosen the entry in column B will change to "TBC" (to be confirmed), awaiting a new date for the exam, which will, in turn be manually entered.
The problem is that as soon as "cancelled" or "possession required" is entered, the code above is lost and so it will no longer then change to "enter exam status" after the new date has passed.
I hope i have explained this well enough to understand.
You could add a new case statement to your first case statement in the worksheet change event.
[HIGHLIGHT=vb]......
Case 2 'B
If isdate(range(target).value) then
range(target).offset(0, 9).formula = ""=IF($B$2:$B$157="","",IF($B$2:$B$157<TODAY(),"ENTER EXAM STATUS",""))""
end if
/HIGHLIGHT]