Dependent drop down lists with restricted values and a free text list

[jchurch]

Aim: To create 6 drop down lists in cell C20 based on the selection made in cell C18. For 5 of the options in cell C18 i want restricted values in C20, i.e. the user can only select values from the drop down list. For only 1 of the options in cell C18 (named "DECK") I do not want a drop down list, instead C20 then becomes a free text cell, allowing the user to input any value.

I have successfully created the dependent drop down lists, using named ranges and data validation with the INDIRECT formula. But i cannot get my head round how to allow a free text cell only when the "DECK" option is selected in cell C18. I have tried playing with the error alerts but this seems to allow the user to input any value no matter what selection is made in cell C18.

Is this possible?

 

[oldbrewer]

for the deck option, could you only use the value in Z1 and user could change z1 to anything - z1 would be named as say special case and you define a special case list as only containing the range "special case" ???

 

[jchurch]

for the deck option, could you only use the value in Z1 and user could change z1 to anything - z1 would be named as say special case and you define a special case list as only containing the range "special case" ???

unfortunately I dont think this would work as I need one selection to be made in this specific cell, thanks for the idea though!

 

[Akuini]

Hi, jchurch. Welcome to the Forum

You need vba. Try this:
This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address(0, 0) = "C20" Then
    Target.Validation.ShowError = Range("C18") <> "DECK"
End If

End Sub

 

[jchurch]

Hi, jchurch. Welcome to the Forum

You need vba. Try this:
This is an Event Procedure, you need to put it in the code module of the sheet in question (say sheet1). This is how:
Copy the code > open sheet1 > right click sheet1 tab > select View Code > paste the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address(0, 0) = "C20" Then
    Target.Validation.ShowError = Range("C18") <> "DECK"
End If

End Sub

AMAZING this worked!!!! thanks so much!! I wonder if you might be able to help with next issue. I have written a code so that when all drop down boxes are un-selected they display "Please Select". However in cell C20 i would like it to display "Please Input" if "Deck" is selected in C18, Else "Please Select. However I am not quite sure how to amend the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$18" Then
Range("F20").Value = "Please Select..."

End If

If Target.Address = "$C$18" Then
Range("C20").Value = "Please Select..."
Range("C22").Value = "Please Select..."
Range("C24").Value = "Please Select..."
Range("C28").Value = "Please Select..."
End If

If Target.Address = "$i$18" Then
Range("i20").Value = "Please Select..."
Range("i22").Value = "Please Select..."
End If

End Sub

 

[Peter_SSs]

Another possibility would be something like this. It will remove the drop-down arrow altogether when "DECK" is in C18

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Range("C20")
    If ActiveCell.Address = .Address Then
      With .Validation
        .Delete
        If Range("C18").Value <> "DECK" Then
          .Add Type:=xlValidateList, Formula1:="=INDIRECT(C18)"
        End If
      End With
    End If
  End With
End Sub

 

[jchurch]

Another possibility would be something like this. It will remove the drop-down arrow altogether when "DECK" is in C18

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Range("C20")
    If ActiveCell.Address = .Address Then
      With .Validation
        .Delete
        If Range("C18").Value <> "DECK" Then
          .Add Type:=xlValidateList, Formula1:="=INDIRECT(C18)"
        End If
      End With
    End If
  End With
End Sub

Thanks so much this is really great!! Only one slight niggle is that when you initially select cell C20 after selecting "Deck" in cell C18 the arrow does still appear, it is only when you click again in the cell and it disappears, would be great if it didnt appear at all, do you know if that is possible?

 

[Peter_SSs]

when you initially select cell C20 after selecting "Deck" in cell C18 the arrow does still appear, it is only when you click again in the cell and it disappears,

I don't get that behaviour so I'm not sure what is causing that. Did you remove or disable the previously suggested code before testing mine?

 

[jchurch]

I don't get that behaviour so I'm not sure what is causing that. Did you remove or disable the previously suggested code before testing mine?

I did indeed.

I think I have found the reason although no idea how to resolve it.......if you select an option in C18 that is not "Deck" then subsequently change C18 to "Deck" the issue occurs. If the form is clear (showing "Please Select" in C18) and "Deck" is selected it works perfectly

 

[Peter_SSs]

..if you select an option in C18 that is not "Deck" then subsequently change C18 to "Deck" the issue occurs.

I still don't get the behaviour you describe.

A couple of comments though

1. Your Worksheet_Change code makes changes to the worksheets (when certain cells are changed). When the code makes those changes to the worksheet, the Worksheet_Change code is called again unnecessarily. I suggest that you put Application.Enablevents = False at the start of that event code and Application.Enablevents = True at the end to avoid that needless calling of code.
   
   
2. Have a look at my signature block below about how to post vba code in the forum so that it retains its indentation formatting otherwise it is hard to read/debug.