Circular references?

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!

Sam

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.

Thanks Bryce,

What would need to go into the added column?

would it be easier to add this as vba code? im totally lost :|

Ok...

Give me the conditions you want to trap (ie. 1) if A then B, 2) if C then D, etc.).

How many different things are you trying to trap, and how do the impact each other?

Thanks!

Bryce,
Thanks for your help.

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.

Hope this makes sense :S

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.


I hope this answers your question.

ive been working on this spreadsheet on and off for months! your help is greatly appreciated :)

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"

Which version of Excel are you using?

2003.... unfortunately it is the only version compatible with a very complex macro we have to use for generating tunnel reports

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'

Case "ENTER EXAM STATUS" 'LIGHT GREY'
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1

Case "Cancelled - See Comments" 'RED'
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 1

Case "Possession Req'd - See Comments" 'LIGHT ORANGE'
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 1

Case "Report held by Author" 'LIGHT YELLOW'
Target.Interior.ColorIndex = 36
Target.Font.ColorIndex = 1

Case "Report held by Engineer" 'LIGHT GREEN'
Target.Interior.ColorIndex = 35
Target.Font.ColorIndex = 1

Case "Notes on Server - Unassigned" 'TAN'
Target.Interior.ColorIndex = 40
Target.Font.ColorIndex = 1

Case "Engineer to Review" 'LAVENDER'
Target.Interior.ColorIndex = 39

Case "Uploaded to Server & Alarm" 'LIME'
Target.Interior.ColorIndex = 43
Target.Font.ColorIndex = 1

Case "Unknown" 'Red text'
Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 3

Case "N" 'LIGHT ORANGE'
Target.Interior.ColorIndex = 45

Case "Y" 'GREEN'
Target.Interior.ColorIndex = 10
Target.Font.ColorIndex = 36

Case "OVERDUE" 'RED'
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 36

Case Else

End Select

End Sub

I think what you want for those colors is to use conditional formatting on the cells.

i made them with my limited understanding of coding in order to format the colours in several locations. Does this complicate things further? :S

You can use conditional formatting on the cells you need to affect.

yes, but only up to three different colours, i have 9. can my code and your code not be incorporated?

You should be able to insert your code into his.

After his "End Select," put your entire code in.

I don't think they overlap in any way, since yours is just changing colors.

I am posting again because the thread did not indicate that I had responded recently...

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.

here to suggest

Hello all,

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 :/

Can anybody tell me how to combine the two? ive been playing about with it but since i dont really know what im doing i cant get it to work :(

Did you try what I suggested in post 20?

What does your code currently look like?

@vbfbryce...

He has an exit sub within the select case and this could cause his second select statement to be ignored!

Here to help ( I missed it first time as well!)

Bryce,

I did try as you suggested however it is true what Incidentals said about the second statement to be ignored.

The code currently looks like this:


Private Sub Worksheet_Change(ByVal Target As Range)

Worksheets("Sheet1").Protect AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True, AllowInsertingRows:=True

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'

Case "ENTER EXAM STATUS" 'LIGHT GREY'
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 1

Case "Cancelled - See Comments" 'RED'
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 1

Case "Possession Req'd - See Comments" 'LIGHT ORANGE'
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 1

Case "Report held by Author" 'LIGHT YELLOW'
Target.Interior.ColorIndex = 36
Target.Font.ColorIndex = 1

Case "Report held by Engineer" 'LIGHT GREEN'
Target.Interior.ColorIndex = 35
Target.Font.ColorIndex = 1

Case "Notes on Server - Unassigned" 'TAN'
Target.Interior.ColorIndex = 40
Target.Font.ColorIndex = 1

Case "Engineer to Review" 'LAVENDER'
Target.Interior.ColorIndex = 39

Case "Uploaded to Server & Alarm" 'LIME'
Target.Interior.ColorIndex = 43
Target.Font.ColorIndex = 1

Case "Unknown" 'Red text'
Target.Interior.ColorIndex = 0
Target.Font.ColorIndex = 3

Case "N" 'LIGHT ORANGE'
Target.Interior.ColorIndex = 45

Case "Y" 'GREEN'
Target.Interior.ColorIndex = 10
Target.Font.ColorIndex = 36

Case "OVERDUE" 'RED'
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 36

Case Else

End Select
End Select

End Sub

i have one further problem i would really appreciate your help in solving..

Im trying to make the spreadsheet as automated as possible in an effort to keep it consistent and encourage people to use it.

i currently have the following code so that, if the date of the scheduled exam has passed, a prompt to "ENTER EXAM STATUS" appears.

=IF($B$2:$B$157="","",IF($B$2:$B$157<TODAY(),"ENTER EXAM STATUS",""))

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]

Where would i need to put that? Is it possible to change the date column B to "TBC" if the exam was cancelled or incomplete?