Drop Down List Dependent on Cell Value

[OscartheDog]

Hi!

Sorry to bother you all but I have a question; is there a way to add a drop down list depending on the contents of the cell above?

Basically, I have a cell asking for the 'Type' of advice required - so for example cell A1 would say "Venue". A2 would be drop down box with a variety of options for them to select the venue.

If they select "Other" from the list B1 shows "If Other, State Here:" for them to input freehand information into B2. However if they select "TBC" from the Venue list I'd like it to be a "Yes"/"No" drop down. Is there a way to incorporate this into the same cell so the drop down only appears based on which option they originally suggested?

I'm sorry if that makes no sense! I always struggle explaining it - please let me know if I can provide any more information and thanks in advance for your help!

OscarPup

 

[tico_ocit]

You have two things here first "contents of the cell above" - yes it is possible - and you have "incorporate this into the same cell" - I don't think it is possible, and I don't think it is reasonable... If you would like to share your sheet would be much easier, at least for me to help you.

 

[OscartheDog]

Thank you for getting back to me so quickly. I've added images to try and explain it better. So example one shows the options people have to choose from.

Example 2 and 3 are the ones I'm trying to merge together into B1/B2 if at all possible. So if they select 'Other' from the list, B2 will just allow the user to type anything into the box.

However if you look at example 3 - if they select 'TBC' I would like it to show a drop down in B2 of Yes/No.

 

[tico_ocit]

However if you look at example 3 - if they select 'TBC' I would like it to show a drop down in B2 of Yes/No.

B2 or B3 the Yes/No drop down? I think, not sure, by just excel formula you cannot block or unblock cells.. However we could manage some vba code, let me know something.
The user select "Other" and enter on B2 something. From that, what you do with the text/value on B2? Am I making myself clear?

 

[OscartheDog]

It's combining A2 & A3 into just B2 would have to be a drop down if it was TBC and allow free text if it was 'Other'.

I'm currently working on a vba code but trying to word it isn't going well

I've made literally a start to say that if A1 is TBC then ....

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If UCase(Target) = "TBC" Then
ActiveSheet.Unprotect


It's just trying to find the right way to make it select a list and make a drop down

 

[OscartheDog]

Actually - I've changed it now to:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If Range("A1") = "TBC" Then
With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Sheet2!F1:F4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True

But not sure I'm approaching this the right way

 

[OscartheDog]

All fixed now:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
If Range("A1") = "TBC" Then
With Range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=Sheet2!F1:F4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Else
With Range("B1").Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With




End If
End If

End Sub