Automatically Updating Pivot Tables in Excel 2010

[pendle666]

Hello

I'm new to using Pivot Tables and I've put my first one into a spreadsheet which has details of the salesmens:

cost of goods sold
warranties sold
delivery charges
grand total


This is helping me so far, but what I would like to do is see if it's possible to have the Pivot Table update automatically when a specific cell updates in the spreadsheet - E26 is a grand total cell, so when that updated if the PT could update automatically that would be handy.

Because I have lots of data to enter each day for lots of branches, I have forgotten to update the table and then realised after the event that I've done something wrong - I'm not just being lazy!



thank you for helping

____________
Pendle

 

[SkipVought]

HI,

Is your source data table in the same sheet as the PT?

What you state that "E26 is grand total cell" is that updated by formula or manually? That seems kind of fishy to me, as the PT will be doing all your aggregations, like totals and grand totals.????

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Hello

Yes the source data is on the same sheet as the PT.

The grand total in E26 was there before I started doing PT's. Line 26 isn't included in the PT.

I can always not use that line, it can come out when I know that all is working correctly. But what I want is for the PT to update automatically without intervention from me.

thank you for helping

____________
Pendle

 

[SkipVought]

So you could use the Change event in that sheet. Is your source table a Structured Table? It would also help, in that the TABLE would be the source for your PT, not just a range.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

It is now!

It looks like this:
[pre]

A B C D E
1 Name Sale value Warranty Delivery Total
2
3 Stock
4
5 Joe Bloggs 2399.98 0.00 60.00 2459.98
6 Martha Green 349.97 349.97
7 John Brown 1472.00 160.00 60.00 1692.00
8 John Brown 2469.00 160.00 60.00 2689.00
9 John Brown 519.97 60.00 579.97
10
11
12
13
14 Lates
15
16
17
18
19
20 Cancellations/Amends from other days
21
22 Martha Green -299.99 -299.99
23
24
25
26
27 Goodwill and other discounts from previous days
28
29 Andrew Black -200.00 -200.00
30

[/pre]

Basically what i'm trying to achieve in the long run is a PT which will give me the totals in Stock for each sales person, the totals in Lates, Cancellations and Goodwill.

Does that make sense?

thank you for helping

____________
Pendle

 

[SkipVought]

Your Names field is DUAL USE, as it also has Caregories of some sort. These need their own column for your table & PT to work properly!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

But each is a separate section in their own right, so do I need to put headers in each section? If so, can I use the same names as the top in Stock?

At the end of the report I'm trying to balance, it'll be stock plus/minus all the other categories and sometimes there might not be anything in there.

thank you for helping

____________
Pendle

 

[SkipVought]

One column with the appropriate value in each row.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

Okay I shall try that.

Thank you again for your help. No doubt I'll be back!

thank you for helping

____________
Pendle

 

[SkipVought]

No dates in your table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

No, I only have to do one each day and print it off for the file. Next day it gets overwritten.



thank you for helping

____________
Pendle

 

[SkipVought]

Why???

You are so short-selling the capabilities of tables!

I have to ask. "Where is your daily data coming from?"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

We get a printout for each day from the store, this gives the full breakdown of the sale, each item that has been purchased, cost and sale prices - everything.

From that I need to make up the total for each salesperson their sales, warranties and deliveries

If there are any, the discounts and returns that are attributed to each salesperson.

These totals are then put onto a daily commission sheet - this is another workbook, one for each branch and then a worksheet for each day for the month 1st, 2nd, 3rd and so on.

At the present time, my other two colleagues, just use a pen and calculator and scribble on the printout their totals. But if they miss something it can be a pain to find. That's why I want to use Excel, so the sheet I'm working on is my breakdown. At some point I'll want to transfer my figures into the daily commission workbook, but I need to understand the intracies of the job better.

thank you for helping

____________
Pendle

 

[SkipVought]

So can you get access to the store's system that generates the report?

That would save lots of effort and error.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[pendle666]

I've no idea. I've only been here a month. It's all very manual and I'm trying to automate as I go - just the little things here and there.



thank you for helping

____________
Pendle

 

[SkipVought]

That's ok. Just keep that in mind. You could become an automating hero!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]