Hi All,
Like many others I too amo very new to Xcelcius and need your help desperately.
My data set is like:
Sales sector Business Unit Country Gross Margin
Automotive Longs France 100
Automotive UK Portugal 200
RAIL Longs Norway 300
There should be 2 filters Sales sector and Business Unit (I cannot achieve this functionality using filters as there can be multiple rows for a particular sales sector and BU combination). How can I make the 2 filters cascading using combo box?
You may use Accordion Menu , rather than combo box.
Or else, you may use two combo box; first combo box for sales sector where you must use Filter Rows option as insert type to insert data range for selected Sales Sector. Then you must use this filtered rows for second combo box (for Business Unit) as source and labels.
So you have two combo boxes. They take their labels, respectively, from
A1:A10 and B1:B10
So A1 = Automotive
A2 = Trucks
A3 = Rail
etc.
Now, prepare a table of combo boxes: in C1:E10, prepare the second combo box for each value.
So C1:C10 are the labels for ‘automotive’:
C1 = UK
C2 = France
C3 = Italy
D1:D10 are the labels for ‘Trucks’:
D1 = USA
D2 = Canada
etc.
And E1:E10 are the labels for 'Rail
Now, build the 2nd combo box. Assuming the 1st combo box inserts its chosen label position into $A$11 (so a11 = 1 if automative, a11=2 if trucks, etc.)
B1 = INDEX(C1:E1,$A$11)
B2 = INDEX(C2:E2,$A$11)
etc
(BOB member since 2008-02-25)