Populate dependent listboxes from database on separate wksht

fixitsteve

New Member
Joined
Dec 31, 2005
Messages
3
I have a database in the form of a table on one worksheet. Each field within a record may be repeated multiple times as shown below:

Name1| Size1 | Color1_for_Size1 | subAttribute_forColor1_for_Size1
Name1| Size1 | Color2_for_Size1 | subAttribute_forColor2_for_Size1
Name1| Size2 | Color1_for_Size2 | subAttribute_forColor1_for_Size2
Name2| Size1 | Color1_for_Size1 | etc.
Name3| Size1 | Color1_for_Size3 | etc.

I'd like to programmatically populate Listbox1 (on another worksheet from the original data) with the unique members of the Name field. Dependent on the value chosen in Listbox1, I'd then like to populate Listbox2 with the unique values in the Size field for that value. And so on for multiple (say up to 5) fields. Would the application run faster if the searching for the listboxes was done once and then stored?

I'd appreciate as much help as anyone can give!!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I'm using Excel to do a bunch of calculations in an estimating application. The selection of data using listboxes is to setup the materials portion of the estimation process. You bring up a good point and maybe Access would be good to move to so that I can store quote information over time. I don't think that I'm quite ready for that step yet though.
 
Upvote 0
Hope this is what you are trying to do. http://www.contextures.com/xlDataVal02.html
This will show you how to setup a dependent validation list. There is a sample you can download to give you an idea of what it does. Since you are adding data to this you might consider using dynamic named ranges instead of a regular named range. If you have blank cells anywhere then you will need to look at using a dynamic named range with that hurdle in mind. Let us know if this is what you need.
 
Upvote 0
Thanks for the thoughts. Perhaps taking the database of information and creating the dynamic lists each time the spreadsheet starts up would be more efficient and prevent the population of the lists each time.

If anyone can provide some help with how to get the data from my database on a separate spreadsheet into dynamic lists at startup, that would be a great help.
 
Upvote 0
Welcome to the board. Can you explain EXACTLY what you want to accomplish? I'm trying to get a feel for use of the Listboxes as opposed to other means. If I'm guessing your final goal correctly, I believe MS Query or a Pivot Table will give you the desired results, but I need to know how you are using the ListBoxes and the outcome of the selections.

lenze
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,514
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top