calculating specific data from a pivot table to create a pivot chart in Excel 2016

[cinstanl]

I have a rather large pivot table that shows distributors and YTD sales. I need to compare current YTD to last years YTD for the same amount of weeks. An example would be: According to their calendar they just finished week 21. When you create a pivot table it brings all of the data from week 2015. I need a chart that will only compare the first 21 weeks of 2015 to the current 21 weeks. Also, I need it to be able to automatically update with the next week... so when we complete week 22 it will pick up last years data for week 22.

I am not sure if this is even doable. I have looked everywhere online and cannot seem to find any assistance. There are other calculations regarding the quantity of distributors that so far I have been able to figure out.

If this is doable, than I can attach a file.

Please Help!

[dflak]

Instead of building the chart off the pivot table, build the chart from named dynamic ranges overlaid on the pivot table. That's the best advice I can give without actually seeing the pivot table.

Here are a couple of articles that may help:
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

[cinstanl]

Thank you for responding. I think I may have partially figured out what I need to do. The only issue that I am having at this time is trying to figure out a way to tell the formula that the first set of numbers are calculated to stop the calculations and calculate the next set of numbers, without manually having to enter the formula. I will send an attachment this time. What I am asking is... I need the average percentage for each distributor, however, excel does not know how far the calculations need to go before it moves on to the next distributor. It's a little hard to explain. I tried to add an attachment but the option, when selected does nothing. Do you know of some other way that I can add an attachment or do you want me to send you a screen print, it would be rather large. I have sent an email to the Excel Forum support to see why it is not allowing me to add the attachment.

If you know a way of telling Excel to add a certain amount of rows based upon if the distribute changes, please let me know.



If not, please let me know that as well.

Cinstanl

[dflak]

If go advanced -> Manage attachments doesn't work, try compressing the file and sending the zipped file.

[cinstanl]

It doesn't matter if it is zipped, the actual button to send an attachment is not working. When selected it brings up a little white box that is about, 1/8 inch high, does not give the option to browse for the attachment. I have an email into support to see why it will not give me the option to send an attachment. I am in the "Go Advance" screen though. The selection for the attachment is there, just not working.

Cindy

[dflak]

I really do have to see how the data is laid out. Unfortunately, I can't see most pictures posted here.

[cinstanl]

As soon as the Excel Forum Support assist me, I will resend with the attachment.

Cinstanl

[cinstanl]

Do you have a direct email for me to send the attachment to... or is that a no no on this forum?

cinstanl

[dflak]

Did you ever get your attachments working?

[cinstanl]

I think that I convinced them to do the original issue that I had differently. So you can disregard the question.

[cinstanl]

Thank you.

I found another work around. I think I spend most of my time finding work arounds. I am getting ready to post a question regarding create charts or graphs from a listbox selection.