Excel - Dynamic drop down from Access database

Posted on 2011-04-29
Last Modified: 2012-05-11
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
Question by:holemania
    LVL 9

    Assisted Solution

    LVL 41

    Expert Comment

    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.



    Author Comment

    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 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.
    LVL 41

    Expert Comment

    Ok - will take a look at direct connection - should not be a problem...

    LVL 41

    Accepted Solution

    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!




    Author Comment

    Thank you.  I think i can work off what you have provided.  Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now