Excel combo chart assistance

**PRodgers** · 08-28-2009, 08:06 AM

I have created a simple chart in excel and i have added a combobox to update the chart based on the selection from the combobox and this works fine. However the combobox does not group together the group selected from the combobox. My combobox is populated with the following values:

Car
Bus
Lorry
Motorcycle
Tractor

I want the chart to group together all the vehicles ie all the "Cars" from the data, at present it only displays one row of data per chart. Can anyone help, i have included my workbook below:

**Andy Pope** · 08-28-2009, 08:40 AM

Try replacing the INDEX formula, which will only return a single record, with

C2: =SUMIF($A$6:$A$29,$B$2,B6:B29)

**PRodgers** · 08-28-2009, 09:57 AM

The chart isnt updating when i select a value from the combobox even before i tried the above formula, i not sure what has happened.

**Andy Pope** · 08-28-2009, 10:09 AM

the file you posted had calculation mode sset to manual.

Tools > Options > Calculation > Automatic

should make it update

**PRodgers** · 08-28-2009, 10:15 AM

Originally Posted by Andy Pope

the file you posted had calculation mode sset to manual.

Tools > Options > Calculation > Automatic

should make it update

Thanks Andy, i couldnt work out where i was going wrong

**PRodgers** · 08-28-2009, 10:50 AM

Andy im trying to add a second combobox to filter the data, i have added another column called "Fuel", do i use VLOOKUP or could i use the INDIRECT function to filter for ie "Car" and "Petrol"?

Here is the worksheet im using:

**Andy Pope** · 08-28-2009, 11:33 AM

Now you need to use SUMPRODUCT

C2: =SUMPRODUCT(($A$6:$A$29=$B2)*($B$6:$B$29=$C2)*(C6:C29))

Also the new combobox needs to output it's selection to a different cell. Otherwise both combos will change at once.

And you need to change the INDEX formula

B2: =INDEX(Vehicle,$A$2)
C2: =INDEX(Fuel,$A$3)

**PRodgers** · 08-28-2009, 11:45 AM

Originally Posted by Andy Pope

Now you need to use SUMPRODUCT

C2: =SUMPRODUCT(($A$6:$A$29=$B2)*($B$6:$B$29=$C2)*(C6:C29))

Also the new combobox needs to output it's selection to a different cell. Otherwise both combos will change at once.

And you need to change the INDEX formula

B2: =INDEX(Vehicle,$A$2)
C2: =INDEX(Fuel,$A$3)

Andy thankyou for you help, i really appreciate it, that works well