• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

updating pivot table - error?

Attached is a workbook with monthly sales figures.

The sheet entitled 'pivot table' is slightly dated as it doesn't include the June figures.

I am basically trying to add the June figures to the pivot table so it continues in the exact same format as for the previous months.

I have added the June data to the source data (FY2014) for the pivot however when I try to up date the pivot table I am not getting the desired result.

Can someone help?
Master-YTD-3.xlsx
0
robmarr700
Asked:
robmarr700
  • 4
  • 2
  • 2
2 Solutions
 
Rob HensonIT & Database AssistantCommented:
Your pivot table data source is only looking at the source data down to row 15132 but the data goes down to row 21781. You need to extend the range that the pivot table is looking at. When in the Pivot table there will be an extra tab on your Ribbon for Pivot Table tools with two tabs, one of which will be Options. One of the buttons on that tab will be Change Data Source. Click this and a Range chooser window will open up with the current range shown. Use this to extend the range, if you press Shift + End + Home, it should select the required Range.

Alternatively, you can setup a Dynamic named range and have the Pivot table look at instead.

Thanks
Rob H
0
 
robmarr700Author Commented:
Sorry perhaps I wasn't clear. I've got as far as that but this is where the problem occurs. If you see the attached sheet you will notice the pivot data is in not continuing in the same way as it was before June was added.

For example, Columns U and V have been added and there is no £Dif or %Dif for june?

Rob
Master-YTD-3.xlsx
0
 
Rob HensonIT & Database AssistantCommented:
When looking at the data sheet I noticed a whole load of blank cells on the left hand side, do these need population with formula to provide the additional data for the pivot?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
robmarr700Author Commented:
Hi Rob,

Yes they do really. I wouldn't see why they would be an issue though seeing as they were still present even before the June data was added?
0
 
Jerry PaladinoCommented:
robmarr700,

I understand your issue and tried several times to get the Pivot Table to update with the June data.   I don't know if the Pivot is corrupt in some way but I recreated it in a new tab called "NEW PIVOT REPORT" and it works properly.   In the attached file, I also converted the FY2014 data tab to an Excel Table to make adding data easier.   You can convert a list of data to an Excel table using the INSERT menu and selecting TABLE in the Tables group or with the keyboard shortcut CTRL-T.   When converted, it named the table TABLE2 so I used that defined name as the data source for the Pivot Table.   As you add new data or remove data the Table reference will keep track of the rows and columns so you do not have to continuously change your pivot data source range.

Also, I noticed that TS-diff and TS-pct both refer to the column "O" value so they are exactly the same.   I checked both the files you uploaded and they both are that way.   Might be an error if they should be different.

Thanks,
Jerry
 Master-YTD-3-NEW.xlsx
0
 
robmarr700Author Commented:
Thanks for looking at this Jerry, the updating process will certainly be useful.

Just a couple of things I have noticed.

1. A number of the customer names were duplicating in the pivot due to some slight differences in the source data. I have cleaned these up and reattached.

2. Columns U and V (total £ Dif and total % Dif) are not necessary as the total sales figure is not being compared to any previous data. TS-diff and TS-pct are supposed to be the same.

3. I have notices that the £ Dif and % Dif columns are not displaying the correct figures.
They should display the difference in number and percentage from the previous months sales.

I have attached the original pivot I had created for me. The pivot used the columns TS-diff and TS-pct to calculate the differences in the pivot. Quite how I'm not sure.

If you could please edit the attached master sheet as it has the amended customer names.

The link below shows the initial question I had set up for this project if you want something to refer back to.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28458489.html#a40198593

Thanks
Rob
Master-YTD-3-NEW.xlsx
Orginal-pivot-data.xlsx
0
 
Jerry PaladinoCommented:
Rob,

I read through the previous question where Glenn was working with you.   I have to say, I am a bit confused.   This question was related to the Pivot Table not updating yet in the previous question with Glenn, he suggested you get rid of the Pivot Table because it was not being used.   In the attached file on that previous question, TS-Diff and TS-pct are calculated the same way as the file I uploaded for you in the FY-2014 data tab which is the data source for the Pivot Table.    It appears Glenn may have created a separate worksheet and did not use the Pivot Table.

I think it might be best that I leave this for Glenn when he returns tomorrow on the 17th.   It seems he may have the full background on this questions and may be a quicker source to your answer than you and I starting from scratch.

Thanks,
Jerry
0
 
robmarr700Author Commented:
No Problem , thanks for your help. Keep a look out for my future questions, I think they will suit your expertise.

Rob
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now