Creating two charts linked to one pivot table?

[theysaytrue]

Hello. I have not been able to figure out a way to set up a second chart which is linked to the same pivot table, without it being the same exact chart.
I work in Excel 2007.

Here is a link to the document. https://docs.google.com/open?id=0B8rs9OQkod0qTmF5cU85QnZMV2c

I want to make two separate charts; one for Sum of Hours per period end date, the other for Sum of Amount per period end date.

Help please!

 

[cyrilbrd]

Hi,
something like that? link.

 

[theysaytrue]

Yes, just like that!
What did you do?!

 

[theysaytrue]

Actually not JUST like that.
I see what you did now. But when the pivot table filters are changed (I removed the raw data from the link I posted, but there is a lot of it), then the chart still only shows the range from 40:47.
I need the charts to both work to show all the information the pivot table is displaying.
And if I set it to do that when both filters are set to "All", then when I use a filter the chart is full of blank space.

Is there a way to do this?? There must be!

 

[cyrilbrd]

Ok, please check if this is closer to what you need, I however modified your pivot and removed the grand total as this would interfere with the chart.

Would that work for you?

Link to new file.

 

[theysaytrue]

Omg, I just typed the longest reply and clicked the wrong button and lost it all -__-
In short, I'm extremely impressed by your solution. It really works, but the grand totals are something that is necessary to the report that my boss wants.
Where did you come up with all that "!∑hours" stuff? I've never seen it before, BRILLIANT though.

I was able to find something that worked for me the other day, though it's certainly more complicated than your solution. I'll be sticking with it though. It's an example macro that I modified; I found it here http://www.excelforum.com/excel-general/497771-synchronize-pivot-table-page-fileter.html

Cyrilbrd, thank you SO much for taking the time to help me figure this out. I KNEW it could be done!
Again, where did you get those formulas from? How did you learn it could be done that way? because I never knew!

 

[cyrilbrd]

well you could have used a Subtotal (9,range_of_the_pivot) set to the lower limit of the maximum range that your pivot could reach and modify the range of the Named Range to fit only this, but this said the macro is quite cool and seems to fit your needs!

Glad you found a solution.

Cheers!