Drop Down Lists

[WagnerE]

Hi there,

This is my first time posting and really have little in-depth experience with excel. With that said, I am tasked with creating a spreadsheet for calculating some estimate numbers at my work. So here is my dilemma.

I have a cell in A1 that is a drop down list. Based upon what you pick, cells B1 through B16 populate with either text or are left blank. What I am having trouble with is that cells C1 through C16 need to have a drop down list in them, but only if there is text in the corresponding B cell. So if B10 has text, C10 will now have a drop down list of values available, but if B10 is blank, I need cell C10 to be blank as well.

I have scoured through the internet and many forums, but all I have been able to find are drop down list dependent upon drop down lists, and that doesn't seem to cover the reverting back to blank status.

Thanks for you help.

 

[RCBricker]

I would use named ranges and use those to select your lists.

If you want the list dependent on the results of the above cell then use an if statement in the data validation box.

Select data validation
select list

in the text box type something like

=IF(b10="","",[your named range here])

 

[WagnerE]

RCBricker,

I went with your suggestion, the problem is that they want to use the sheet multiple times in the same session, changing which B cells have text in them. Using that formula, the C cell stays on the last option that you have picked once there is no text in the B cell. Now, I could have a blank option in the C cell drop down list and have to change it before using the spreadsheet again, but I know that the higher ups will want some kind of automation where they change the option in the A cell, and it clears out the previous information in the C cells.

 

[RCBricker]

Hey WagnerE,

I looked around and found the following code. It is an worksheet change event so it needs to be inserted into the worksheet that houses the dropdowns. in the VBE just select the worksheet by double clicking the name fo the sheet in the VBAProject window. Then paste the code into the sheet.

****NOTE you need to change the target.row = 2 to the row number that houses the conditional drop downs.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Row = 2 Then  'change this number to the row that houses the drop downs
  If Target.Validation.Type = 3 Then
   Application.EnableEvents = False
   Target.Offset(1).ClearContents
  End If
End If

exitHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

rich

 

[davyoy306]

Rich,
I double clicked on sheet1 under the Excel objects under VBAProject, pasted the code above in and changed the row to 19. However, the cell contents do not delete when the other cell is changed. I can only see 1 difference; there are 6 drop downs on the sheet and the width of the selection list that appears when clicking on the drop downs on all of them are now all as wide as the widest drop down, rather than them each being the same width as the cell it is in (hope that makes sense!).
Any ideas?
Thanks, Dave