If cells b5, d5, and/or g5 are empty (one or all), run a formula to calculate a due date

[eabaker64]

Hi. I need a formula in B3 to look at cells B5, D5 and G5. If any of those cells are blank, a formula is run which calculates a date that is 15 workdays after a date showing in cell B2. If cells B5, D5 and G5 all contain data, the formula does not run and instead returns a value of N/A in cell B3. I have this formula but if any of the B5, D5, or G5 cells have data, it returns N/A. I don't want it to return N/A unless all three cells contain data. Any ideas?

=IF(B5<>"","N/A",IF(D5<>"","N/A",IF(G5<>"","N/A",IF(B2<>"",WORKDAY(B2,15),""))))

 

[dreid1011]

I have not worked with VBA for several years and even then, it was hunting through forums and videos to find what I needed. I am not having as much luck with this in trying to figure out a VBA code to do what I want. Any ideas? And if not, thank you for all you have done!

Yes, I have some ideas, but I am also thinking there might be a formula option, but I think I need to clarify the logic.

Assuming data is entered in order (B, D, G), and D and G won't get data until B has data?

When is row 9 filled in? When should B7 calculate the first time?

Once B7 has data, then D7 can calculate?

Will B7 ever be N/A before D7 is calculated?

 

[eabaker64]

Correct, D & G won't get data until B has data...and they may never get data if all issues are resolved the first time around. Row 9 would be filled in once all issues have been resolved for Data Validation, Program Monitoring, and sometimes Fiscal. We send a report to a provider which contains issues with Data Validation, Program Monitoring and Fiscal findings. They (providers) have 15 working days from the date the report is sent to fix the issues and send the report back to us with their documentation. Once we get the report back the first time, we have 15 working days to look over the responses, and if all issues are not resolved, we send the report back to them (hence B7). If they fixed all issues and a response does not need to go back to them, then B7 would show either N/A or Done. If all issues are not resolved, then we get into column D and go through the process again. We could have B9 show one date for being completed, but D9, or G9 may be resolved later and have different dates, or visa versa. That is why I want B7 to retain the date instead of showing N/A or Done so that we can see the report had to go back the first time around and issues were not completely resolved until the 2nd go around, at which time D7 would show N/A or Done.

Thank you for your assistance with this. And have a good 4th. I probably won't be back on until Monday, July 8th.

 

[dreid1011]

I apologize for the delay. I am working on this, however, I am not sure how to move forward. I have it entering the follow up dates, but to do it automatically, they are entered after the first entry into row 9, instead of waiting until row 9 is done. The only way I can think of to stop this, is to add some prompts as you are entering row 9 dates.
IE:
Enter date into B9, message box prompt asks if you are going to enter date in D9 (yes/no), select yes,
Enter date into D9, message box prompt asks if you are going to enter date in G9 (yes/no), select yes,
Enter date into G9. All done, no more prompts, B7, D7, G7 remain empty.
Select no at any point, then B7 fills in.

Or have a button to run the code after you are done entering row 9 dates if you only entered B9 and D9.

However, here is another tricky part. If DV and Program Monitoring are complete after the first round, send it back for Fiscal Monitoring to fill in 1st follow up date, but then they still don't complete Fiscal Monitoring... there is no event to trigger the code to fill in D7 for the second follow up because you won't be entering any new dates into row 9.

Long story short, I am not entirely sure how to make this the most intuitive and cover all the possible scenarios without you having to input some dates in row 7 manually.

Or we can make a userform to enter the dates each time, with other options to fill in row 7 appropriately.

What do you think?

 

[eabaker64]

I like the idea of the first option. And we can actually go with just data filled into B9 and D9 as G9 (Fiscal) is not really on the same tracking schedule as B9 (Data Validation) and D9 (Program Monitoring). But if B9 is yes, and it asks for D9, which is a no (or visa versa), I need it to populate the date for B7 as a report will be required to be sent back. And once a date is manually entered for B8 for when that report is returned, I would want it to populate the date in D5 to show when it is due back to us. Once a date is entered into D6 as to when we receive it back again, that would be the next opportunity for a date to be entered into D9. If a date is entered into D9 because all issues are resolved (and B9 was already completed), it then would not put a date in D7 since no follow-up is required, but it would leave the date that was in B7. I need to have a way to track when the due date was for each response, even once the dates are entered into B9 and D9 to show all issues have been resolved. And of course, if all issues still are not resolved, a due date would populate in D7, G5 and G7, depending on the manually entered dates, or when the date is entered into D9. I hope that helps??

 

[dreid1011]

Is there a chance you might send it back before Data Validation is complete?

 

[eabaker64]

Sometimes Data Validation will be completed before Program Monitoring. Other times, Program Monitoring may be completed first. Sometimes they are both completed at the same time, after the first response. Sometimes, neither are completed until after the second response is received.

 

[dreid1011]

I apologize for the delay in posting this, but here is what I have so far. This will work through col B and col D up to D7. If you want to also have it work through col G, I can add that. Let me know what you think.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" Then
        Select Case Target.Address
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case Else
                GoTo ExitNow
        
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub

 

[eabaker64]

I apologize for the delay in posting this, but here is what I have so far. This will work through col B and col D up to D7. If you want to also have it work through col G, I can add that. Let me know what you think.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" Then
        Select Case Target.Address
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case Else
                GoTo ExitNow
       
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub

Okay...Like I said, it has been a while since I've done anything with VBA code. I opened Developer, created a Module, copied the code into the module. Do I leave the formulas that I had in the worksheet there, or remove them? And do I need to refer to the specific worksheet? This workbook will actually have 26 worksheets with identical cells...just different information (dates) added for each worksheet. And thank you for all the work!!

 

[dreid1011]

Okay...Like I said, it has been a while since I've done anything with VBA code. I opened Developer, created a Module, copied the code into the module. Do I leave the formulas that I had in the worksheet there, or remove them? And do I need to refer to the specific worksheet? This workbook will actually have 26 worksheets with identical cells...just different information (dates) added for each worksheet. And thank you for all the work!!

It needs to be placed in the sheet module, and you will need to put it in every sheet. By doing this, it does not need to reference the sheet specifically. I'm not sure about the formulas, you might try it without them though.

 

[eabaker64]

It needs to be placed in the sheet module, and you will need to put it in every sheet. By doing this, it does not need to reference the sheet specifically. I'm not sure about the formulas, you might try it without them though.

Okay.