How to Use a single combo box to generate two graphs

23 Views

Hi All,

I have my data as below

Company Group Week Qty

CP01 16 40/2010 200

CP01 16 41/2010 210

CP01 16 42/2010 220

CP01 16 43/2010 230

CP01 16 44/2010 240

CP01 16 45/2010 250

CP01 16 46/2010 140

CP01 8 40/2010 300

CP01 8 41/2010 310

CP01 8 42/2010 320

CP01 8 43/2010 330

CP01 8 44/2010 340

CP01 8 45/2010 350

CP01 8 46/2010 140

CH13 16 40/2010 100

CH13 16 41/2010 210

CH13 16 42/2010 220

CH13 16 43/2010 230

CH13 16 44/2010 240

CH13 16 45/2010 250

CH13 16 46/2010 260

CH13 8 40/2010 100

CH13 8 41/2010 310

CH13 8 42/2010 320

CH13 8 43/2010 330

CH13 8 44/2010 340

CH13 8 45/2010 350

CH13 8 46/2010 360

I have to display the company level qty for each week against the Group data. I have a combo box for the company and on selection my destination will have all rows with that company, and on the destination rows as a source for the second combo box to choose a smaller set of data for the group and I draw a line graph.

But the user wants a baseline Graph, for example If they choose CP01, all the groups 16 & 8 qty will be summed up by the company. So I will have CP01 for 40/2010 I will have 500 Qty (300+200) so the base column graph will be 500 and depending on the Group selection it will be either 300 or 200.

I am able to the Group within the Company, but unable to do the company baseline. I think it should be done within excel using it's functions, any idea would help.

Thanks,

Arthur.