Posts by josh32

  • Update cell based on value in another cell

    Re: Update cell based on value in another cell


    I didn't make the change that you mentioned below because I don't mind those numbers being there. So the cells in column I do turn blank once there is a completion date noted. Thus, it is working as expected, even when I make a change to the status.


    "I changed the formulas down column "I" to do away with all those days over due in the yet to be used rows.
    VB:
    =If(Or(ISBLANK(F11),ISBLANK(G11),ISBLANK(H11)),"",If(TODAY()>H11,TODAY()-H11,""))
    "

  • Update cell based on value in another cell

    Re: Update cell based on value in another cell


    Wow, thank you NoSparks. The copy you sent back works like I need it to. Even you last question about changing L11 to Complete works just fine. I am able tochange from Overdue to anything else without throwing an error. I'm going to copy all of this code and your column Z formulas back into my original workbook and will let you know if it works.

  • Update cell based on value in another cell

    Re: Update cell based on value in another cell


    forum.ozgrid.com/index.php?attachment/71326/


    Okay, I'm attaching a sample workbook with a lot of the data taken out. But this functionality would still work. So Column L is a data validation list. If the Due Date in H11 is before today (calculated in I11) and there is no Completion Date in G11, it will cause cell L11 to update to "Overdue" with some conditional formatting to turn it red. Then if I go to change L11 to Complete, the error begins.

  • Update cell based on value in another cell

    Re: Update cell based on value in another cell


    This did not work. I apologize because I am a complete novice when it comes to VBA. I know what I want to do, but no clue how to tell you what you need to know. If it helps, I can create a sample Excel sheet on a smaller scale and send it to you.

  • Update cell based on value in another cell

    This seems like it would be simple. I have a project plan with due dates for each task in a column. In column "L" I am using a data validation list to show the current Status, giving only the options of Complete, In Process, Overdue, or Not Started. In column "I", I have a formula to calculate if a task is overdue (basically today - due date for any incomplete tasks), and it gives the number of days overdue.


    I would like the Status to automatically update to "Overdue" in column L if the value in column I is > 0. I was able to make this happen using the following code for one cell. The issue is that if I then manually update cell L11, say if the task is now Complete, Excel throws an error, stops working, and restarts.


    So two questions: How do I update the code so that it doesn't break Excel when I update the status, and what changes do I need to make for this code to work on every cell in column L, not only cell L11.


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("I11").Value > 0 Then
        Range("L11").Value = "Overdue"
    End If
End Sub



    First screen is showing that L11 updated to Overdue. Second screen is what happens if I manually select Complete from the drop-down in cell L11.
    [ATTACH=CONFIG]71323[/ATTACH][ATTACH=CONFIG]71324[/ATTACH]