Drop-down lists

New Contributor

Can someone help me to create a drop down list that changes the worksheet to a blank with only the titles shown on the previous drop-down category so I can enter additional information.  For instance:  My category on my worksheet is fruit.  I then have a Subcategory that has a drop-down list with apples, oranges and pears.  I want to be able to click on the Apples category and enter product codes and descriptions.  At this point, I'm able to click on Apples and see the product codes and descriptions, but when I click on the Oranges category in the drop-down, it has the apples product code and descriptions.  How do I get it to change based on the subcategory selected in the drop-down?

6 Replies
Thank you, Hans - I don't see anything in these that matches what I'm trying to do, but I appreciate the extra info.

@Maggie_Monteleone 

If you would like more help, please attach a sample workbook without sensitive data, and include a detailed description of what you want.

@Hans Vogelaar - Thank you for your help.  On the attached Worksheet, Claims is only one of many subcategories in the drop down list.  I'd like for the blue titles on the worksheet to stay, but the text that I entered to be cleared when I click on another subcaterogy, so that I can enter the correct text for that subcategory.  I want only the titles and that text to be visible when someone clicks on that subcategory.

@Maggie_Monteleone 

That would require VBA.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the code window.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("C3"), Target) Is Nothing Then
        Application.EnableEvents = False
        Range("C9:C9,C23:C24").ClearContents
        Application.EnableEvents = True
    End If
End Sub
Thank you, again, Hans. Much appreciated.