Linked combo boxes

N

Nemo

Hi,

This is my problem. I must create four combo boxes to display unique data
(from numerous duplicate entries), where the data displayed is dependant upon
the previous combo box. An example of data may be:

Class Category Sub-Cat Type
Joe Truck Lease 2007
Joe Van Rent 2005
Joe Van Sale 2008
Pete Car Sale 2004
Pete Truck Lease 2008
Pete Truck Rent 2004
Pete Van Sale 2006
David SUV Lease 2007
David Van Rent 2007
Jenny Truck Rent 2005
Jenny Car Lease 2001
Jenny Car Sale 2008
Niel SUV Sale 2004


I select JOE -> then a choice of TRUCK or VAN, then if TRUCK, -> LEASE ->
2007, or if I selected VAN, -> RENT or LEASE or SALE, and the appropriate
year respectively in the last combo list. I've gotten sooooo complicated,
with one problem being the blanks between unique entries, once I've
eliminated the duplicates. How do I get to shorten the lists to actual data
only?

My apologies for the questions. I have searched the forum extensively, and I
believe I need a hybrid solution, which is currently beyond my means and
imagination!

Thanks in advance for any help.

Nemo
 
M

Max

Think a pivot table (PT) could immediately give you 99%** of the
functionality that you're after. And it takes only a few seconds to set it up.

Select any cell within the source table, click Data > Pivot table ...
Click Next > Next to proceed to step 3 of the wizard.
In step 3, click on "Layout"
Drag n drop Class within PAGE area. Repeat for Category and Sub-Cat. Place
these below the other.
Drag n drop Type within ROW area.
Drag n drop Type within DATA area. It'll appear as Sum of Type. Double-click
on it, change it to "Count" under Summarize by, click OK.
Click Finish.

Go to the PT sheet.
It'll look like this, with selectable droplists all nicely done up:

Class (All)
Category (All)
Sub-Cat (All)

Type Count of Type
2001 1
2004 3
2005 2
2006 1
2007 3
2008 3
Grand Total 13

Eg: If you select Joe-Van-Rent from the 3 page area droplists, you'd get

Class Joe
Category Van
Sub-Cat Rent

Type Count of Type
2005 1
Grand Total 1

**The "1%" functionality not given by the PT is that the 3 droplists are not
dependent.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top