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!!
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

rnickel

New Member
Joined
Dec 26, 2005
Messages
8
This would be an awful lot easier to do in Access. Are you married to Excel for some reason?
 

fixitsteve

New Member
Joined
Dec 31, 2005
Messages
3
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.
 

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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.
 

fixitsteve

New Member
Joined
Dec 31, 2005
Messages
3
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.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,000
Members
412,353
Latest member
SofiaV
Top