Drop Down Menu based on Cell Condition

Question

Hello. I've done my research but can't seem to make this work. I'm looking to have a range of cells be formatted with a dropdown list IF a condition is met in a different cell. See the OneDrive example linked. IF E2="On Time", then no action taken in cell F2 (or a drop down with no/blank option is fine in F2). However since E3="Closed Late", I'd like F3 to offer a drop down menu displaying the options from range $H$2:$H$5. Would like the ability to do this on a large range of rows all at once, or be able to fill down.

Any help appreciated!

https://1drv.ms/x/s!AirBLZsb6b0Hehjrw9WdYcSbuZA?e=xVapag

Answer 1

Really appreciate the help. Was hoping not to go the macro route so I will develop a different two step solution instead. Thanks for the solution.

Answer 2

Hi there

AFAIK, We can not type a Formula and apply a dropdown Data Validation rule in a cell at the same time

To help you with your request/scenario we might need to use VBA macro for the Worksheet event to achieve that

1. Save your workbook as an Excel Macro-Enabled Workbook (* .xlsm)
2. Paste the code below on the Worksheet event VBA panel as indicated in the pictures down below

Private Sub Worksheet_Change(ByVal Target As Range)

Dim statusRange As Range

Set statusRange = Range("E2:E500")

If Not Application.Intersect(Target, statusRange) Is Nothing Then

    If Target.Value = "On Time" Then 

            With Range("F" & Target.Row).Validation 

                             .Delete 

            End With 

            Range("F" & Target.Row).Value = "" 

     Else 

            With Range("F" & Target.Row).Validation 

                     .Delete 

                     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=**$H$2:$H$5**" 

                    .IgnoreBlank = True 

                    .InCellDropdown = True 

                    .InputTitle = "" 

                    .ErrorTitle = "" 

                    .InputMessage = "" 

                    .ErrorMessage = "" 

                    .ShowInput = True 

                    .ShowError = True 

             End With 

    End If 

End If

End Sub

You may find the copy of your workbook with the answer in the link below

https://we.tl/t-ISrvzIGf9C

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany