Excel - Dynamic drop down from Access database

Hello experts,

Just started working with excel and need help in populating a few drop down.  I have an excel file with a spec sheet for T-Shirt.  I would like to create drop down list for each selection from an access database.  Base on the selection for that table, I would like for it to limit(dynamic) items for the next selection and so on.  Pretty much just a filtering down of selections and give me the final product.

I need help with bounding each column in the database to a cell in excel as a dropdown.  How to filter it so that it shortens the list down as selection is happening.  Any examples would do.  I provided an example excel file layout and access database.  This is only a small portion of a huge project, and if I can learn how to accomplish this part, that would help tremendously. TSHIRT-SPEC.xls TShirt.mdb
Who is Participating?
Ok - this was not too difficult (had to re-school up on connecting to Access as its been a while).

The attached works as originally designed, except there's a link to the database and only results associated with populating the range for the DV list is brought in (ultimately, could be a hidden sheet).  You could as well pull directly into the combobox, but going down this path at least first allows us to keep the queries relatively simple and use the generic utility capability from   http:/A_5062.html.

Let me know what you think...  If you can take it from here - great.  If you'd like further assistance (and like this approach) - that's great, too!



Ok.  I leveraged some techniques from a recent question I answered here: http:/Q_26964006.html, leveraging techniques in a past article I wrote, here:  http:/A_5062.html.

I'm not an access expert, but I created a data connection to your sample MDB and then put a list to it in the Product Connections Tab, with a criteria range on top all set for advanced query.  To the right of that is the result of queries to populate the DV lists, which have dynamic ranges linked to them for Sleeve, Size, Color, etc...

If you like this approach, I can help with the rest, or you can take it from here.  Didn't want to go too far as your Access skills from Excel may outweigh what I've done.

This demonstrates how to use dynamic drop down lists with a data validation list base, with cascading (that's why I'm doing the advanced filter query) and I've put all the code modules in the sample spreadsheet attached.

Give it a shot and let me know if you need further assistance, would like to pursue this route, or that I'm way off base :)

I can provide more in the way of explaining how it works, but getting this out quick for you to test.


Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

holemaniaAuthor Commented:
Thanks for the information and help.  I will take a look and update.


I tried the excel file you sent, but for some reason it's not working.  I noticed that you also create a link from excel to the access database.  Then pull the data to an excel spreadsheet.  Is it possible to connect directly to the database and then bind the list to the drop down?  

I'm afraid the excel file will get huge if I start dumping data to one of the worksheet to work with.  I can do this easily in windows vb.net form and bind data to a drop down/combo box.  Just not sure how this is done via excel.  

If an example can be provided where there's a direct connect to the access database, bind to a drop down or cell that is treated like a drop down, then filter base on selection.

Both examples provided, I can see how it is done via having the data in a worksheet and treat it as a drop down.  I'll play around and see what I can come up with.
Ok - will take a look at direct connection - should not be a problem...

holemaniaAuthor Commented:
Thank you.  I think i can work off what you have provided.  Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.