Charting data that needs to be consolidated?

[amsgwp]

Using Excel 2007:


Say I have a list of sales by employee. Employees can have more than one sale, so eache mployee might have 2 or maybe even 5 sales in a month. When I try to chart this information I only want each employee listed once. So the sales need to be consolidated. I made a subtotal for each employee but the charting functions do not work at all. I've tried all kinds of combinations for input data.

Here is a sample of the data. (names are not real)

Please Login or Register  to view this content.

[teylyn]

Hi,

you can create a pivot table and pivot chart for that kind of work. Check out Pivot Table in the help files, and there are lots of threads in this forum, too.

cheers

Teylyn

[amsgwp]

Exaclty what I was looking to do. How in the world did I not think about pivotcharts!

Now, making that work as a macro is a new challenge.


It looks like I need to replace the references to sheet1 so that can dynamically change. If Sheet1 has already been created then it will error out and the create sheet command will create a sheet2 and then the macro will be lost.

Please Login or Register  to view this content.

[amsgwp]

Solved! I can't figure out how to change thread title to say Solved.

Set TableDestination to "" and it auto puts it on a new sheet. Thanks for the help.

Please Login or Register  to view this content.

[teylyn]

Wow! All that VBA goes right over the top of my head , but I'm glad I put you on the right track

Solved! I can't figure out how to change thread title to say Solved.

Try to edit your original post and then look near the title where there should be a drop down box for "prefix". Here you can select "[Solved]". But you can only edit your original post for a limited time. After that, you can contact the mods and they can mark it for you.

cheers

Teylyn