hi everyone, I have a tricky problem...well it is for me 
I currently have a large quantity of data that is in the format below:
Category Detail January February March ........
Fruit Apple 10 12 16
Fruit Banana 42 1 43
Veg Carrot 12 5 6
Well, the data is a little more complex then that but that is a pretty good idea, there are more fields, some calculations etc.
I need a pivot chart that will work as a date series, that can be filtered by a category or detail to get a feel for the data over time.
I really want the fruit to appear as a stack in the chart, with each category having a stack of its own (when filtered there would only be one stack of course)
When inserting a regular chart it is fairly close to right, with dates working, but in a pivot chart it has problems due to the dates being in different collumns.
Is there a way to make this work? If you think the only way to go is using VBA (recording macro and tweaking) then that is ok, I can do that. But an idea of how you might go about it would be greatly appreciated. I can't off hand think of a way to fully automate the conversion (the amount of data changes each day but the number of columns remains the same). I hope my post isn't too long
Any help would be greatly appreciated.
Luke
I currently have a large quantity of data that is in the format below:
Category Detail January February March ........
Fruit Apple 10 12 16
Fruit Banana 42 1 43
Veg Carrot 12 5 6
Well, the data is a little more complex then that but that is a pretty good idea, there are more fields, some calculations etc.
I need a pivot chart that will work as a date series, that can be filtered by a category or detail to get a feel for the data over time.
I really want the fruit to appear as a stack in the chart, with each category having a stack of its own (when filtered there would only be one stack of course)
When inserting a regular chart it is fairly close to right, with dates working, but in a pivot chart it has problems due to the dates being in different collumns.
Is there a way to make this work? If you think the only way to go is using VBA (recording macro and tweaking) then that is ok, I can do that. But an idea of how you might go about it would be greatly appreciated. I can't off hand think of a way to fully automate the conversion (the amount of data changes each day but the number of columns remains the same). I hope my post isn't too long
Any help would be greatly appreciated.
Luke
