Filtering data into charts

[ts04]

Hi!

Using: MS Excell 2000

I have a Crystal Report that exports data to an excell sheet. The data looks like this:

Prod.Code Amount
12345AB 12
34234AB 09
54521BC 90
82383IK 34
84545IK 34
...

I want to create a chart per Prod.Code that ends with AB / BC / IK etc.
I linked the rows next to the data to the values and have them only copy the data if mid(prod.Code),6,3) = AB / BC etc.

Prod.Code Amount Prod.Code Amount Prod.Code Amount
12345AB 12 12345AB 12
34234AB 09 34234AB 09
54521BC 90 54521BC 90
82383IK 34 etc.
84545IK 34


Now I want to create a chart of each prod.Code group.
When I do this it also takes the empty cells which messes up my chart.
Any suggestions how to solve this problem?

The reason why I dont create the charts in Crystal Reports 9 is that I can't put variable fields into a chart.

Thanx for all suggestions in advance!

Tanja

 

[klmorgan]

You could set up some advanced filters for each of the product groups and set the charts over the output from those.

Regards

Keith

 

[KenWright]

Just add another column and use =RIGHT(A2,2) and copy down. Give it a header like ProdCode2. Then just use pivot table on it and break it on ProdCode2.

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------