We help IT Professionals succeed at work.

Excel - Dynamic drop down from Access database

holemania
holemania asked
on
Medium Priority
1,323 Views
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
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Most Valuable Expert 2012
Top Expert 2012

Commented:
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.

Cheers,

Dave
TSHIRT-SPEC-r1.xlsm

Author

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

dlmille,

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.
Most Valuable Expert 2012
Top Expert 2012

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

Dave
Most Valuable Expert 2012
Top Expert 2012
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you.  I think i can work off what you have provided.  Thanks again.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.