Discussion on:

10 steps to adding a dependent list control to an Excel sheet

Is the 10 steps to adding a dependent list control to an Excel sheet available for a file download?
Thank you in advance

Agree with other post...I followed all the instructions to the letter and even the first combo doesn't work...disappointing

I tried to follow the instructions but sadly even the first combo isn't working.

Tell me what's happening or not happening and we'll figure it out.

At first, neither of the controls displayed anything.

In step 1, there was no explicit instruction to name your worksheet, so I named the datasheet as "Lists" to match the reference provided later in the article. To populate the first control, take the Step 2 text "Apply the range name Category to cells A1:C1, as you normally would" as an action step, and not just some text commentary: in the Formulas tab (Excel 2010), click on Name Manager, and set the following: "Name:" = Category, "Scope: " = Lists, and "Refers to:" as =Lists!$A$1:$C$1. For me, that populated the first control.

Populating the second control is still a mystery, and I'd appreciate an update.

I suppose I take the sheet references for granted. I'm sorry for any confusion that caused you.

Thank you for the response. Since this topic is very timely for a problem I'm working on, I'd really appreciate the solution for populating the dependent second control. Thank you for your time with this.

Check the defined names for Dairy, Produce, and Other. (see my latest post)

I get the same result as Kuthumi (above). I can get the first control to populate but not the second.

I tried starting over completely with the same result. I'm not getting any error messages -- just no items in the 2nd list.

I think this could be quite useful for my company. Thoughts?

Thanks

I was finally able to get the second control to display the list of choices when I click the drop down arrow. However, when I select one of the items in the drop down list, that item will not populate the control box. Instead, it remains empty.

Help?

In Step 7, the macro should be attached to the FIRST combo box. Read the instruction "double-click the embedded control and enter the following procedure:" as "click on the FIRST box you created and then click on the View Code button on the Developer ribbon (Excel 2007). The first and last lines of the macro will be filled in for you. Copy and paste the other lines of the code, except the first and last. You might also need break the lines with a stroke of the Enter key as needed."

I think I figured out why most people are having problems! I think one way to avoid the problem in the first place is to add this step between Step 1 and Step 2:

"Step 1A: After typing in the lists, but before naming them in Step 2, rename the sheet "Lists". Now save the file with any name you like as long as it is a macro-enabled file type (.xlsm file for Excel 2007)."

But I think you can fix one you already created by checking the names of Dairy, Produce, and Other and making sure they are correct.

To test if the named range Dairy is working, type the following formula into any blank cell in column D:
=VLOOKUP("Milk",Dairy,1,FALSE)
You should get the result "Milk". If you get an error, your defined name for Dairy is probably messed up. Go to the Name Manager and make sure it looks like it should in Step 2.

P.S. Don't forget that sometimes to make the first combo box work, you need to click on another tab and then back again.