Results 1 to 30 of 30

Thread: Circular references?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    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
    Attached Images Attached Images  

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Circular references?

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    Thanks Bryce,

    What would need to go into the added column?

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

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

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Circular references?

    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!

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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

  7. #7
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

    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?

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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.

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

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

  10. #10
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

    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?

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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"

  12. #12
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

    Which version of Excel are you using?

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

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

  14. #14
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

    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.

  15. #15

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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

  16. #16
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

    I think what you want for those colors is to use conditional formatting on the cells.
    Last edited by dlary9890; Apr 5th, 2012 at 02:13 PM.

  17. #17

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

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

  18. #18
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

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

  19. #19

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

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

  20. #20
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Circular references?

    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.

  21. #21
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Circular references?

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

  22. #22
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Circular references?

    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

  23. #23

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

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

  24. #24

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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

  25. #25
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Circular references?

    Did you try what I suggested in post 20?

    What does your code currently look like?

  26. #26
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Circular references?

    @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!)

  27. #27

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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

  28. #28

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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.
    Attached Images Attached Images  

  29. #29
    Member
    Join Date
    Mar 2012
    Posts
    34

    Re: Circular references?

    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]

  30. #30

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: Circular references?

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width