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]

Alright, try this:

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("B6"), Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

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

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$6"
                If Range("B7") = "" Then
                    Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                End If
            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
                        If Range("B7") = "" Then
                            Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        End If
                        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
                        If Range("D7") = "" Then
                            Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        End If
                        GoTo ExitNow
                    End If
                Else
                    If Range("B9") = "" Then
                        Range("B7") = ""
                    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

It will check if B9 is blank after a value is entered into D6. If B9 is blank and D9 is not blank, it will clear B7. If this is not quite what you want, I am not sure where to go from here. I feel like the logic is going to get really hairy after this, and I just can't wrap my head around it right now.

 

[eabaker64]

Alright, try this:

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("B6"), Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

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

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$6"
                If Range("B7") = "" Then
                    Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                End If
            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
                        If Range("B7") = "" Then
                            Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        End If
                        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
                        If Range("D7") = "" Then
                            Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        End If
                        GoTo ExitNow
                    End If
                Else
                    If Range("B9") = "" Then
                        Range("B7") = ""
                    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

It will check if B9 is blank after a value is entered into D6. If B9 is blank and D9 is not blank, it will clear B7. If this is not quite what you want, I am not sure where to go from here. I feel like the logic is going to get really hairy after this, and I just can't wrap my head around it right now.

Thank you for all that you have done up to this point. It has helped with what I needed and I really do appreciate that!