Linked combo boxes

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nemo - 08 Jul 2007 06:20 GMT

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

Reply to this Message

Max - 10 Jul 2007 03:16 GMT

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.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Hi,
>
[quoted text clipped - 31 lines]
>
> Nemo

Reply to this Message

challa prabhu - 18 Jul 2007 07:38 GMT

Hi,

For information, refer to this url:
http://www.contextures.com/xlDataVal02.html

Challa Prabhu

> Hi,
>
[quoted text clipped - 31 lines]
>
> Nemo

Reply to this Message