Add Data To Pivot Table

  • Thread starter Thread starter Robert Walker
  • Start date Start date
R

Robert Walker

I made a report, requested by our sales reps, that
included sales figures for various customers and
territories over the last few years, showing month
to month totals per rep. But ...

How can I update the report on a monthly basis now?
Instead of having to go back and run the whole report
over again each month, I mean. I just want to be able
to add in the next month's totals to the figures already
in the previous report, but I see no way to 'undo' the
previous setup to add in the new stuff, or merely combine
the new figures with the old.

What am I missing? Any help appreciated.
 
Robert

I'm a little confused as the subject says Add data to pivot table. If this
is true, then if the data comes from an excel range then import the data
each time and re-name the range imported (Insert>Name>Define...). Now use
this range name as the source fro your pivot table and the new data will
appear each time.

If you are using ODBC from an external data source, then the pivot table
should add the new data anyhow.

If that doesn't help, give us a little more detail about the data you've got
and clear up what your doing with the pivot table.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Robert
I'm a little confused as the subject says Add data to pivot table. If this
is true, then if the data comes from an excel range then import the data
each time and re-name the range imported (Insert>Name>Define...). Now use
this range name as the source fro your pivot table and the new data will
appear each time.

If you are using ODBC from an external data source, then the pivot table
should add the new data anyhow.

If that doesn't help, give us a little more detail about the data you've got
and clear up what your doing with the pivot table.
Click to expand...

Thanks. Sorry for the multiple posts, but I asked in excel.misc
earlier and got no response. I won't repeat the error.

Anyway, no, I still don't follow I fear. I saw the insert/name/define
possibility, but it whooshed over my head and I don't see how to
make that work.

I have a report with, say,

customer name
sales rep
date of job
amount of sale

I made a report already, for the past three years, totalling sales
by customer and by sales rep on a monthly basis, so that I can track
a given customer's revenue from month to month, and also compare
this year's revenue in a given month to last year's. Not to mention
the sales reps' total monthly & yearly figures, nice to know also.

So, I now have the numbers up through September of 2004. I want,
each month, to somehow combine the October, Novermber, etc.
figures into the previous file.

Does that help any?

Robert
 
Robert

A little..Is this in a pivot table and if so, where are you getting the
data for this from?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick Hodge said:
Robert

A little..Is this in a pivot table and if so, where are you getting the
data for this from?
Click to expand...

Yup. I run off the report as a .csv file, open it in Excel, then save
it as an .xls file and do the Pivot Table, using the Data + Pivot
Table Wizard. I drag the customer name to the first block on the left,
the sales person to the top as a page separator, the date in the next
block, and the revenue in the last big block on the bottom (Using
Excel 2000 9.0.)

Worked good, it seems, although for some reason, when there
was a zero dollar amount in some of the lines, I got an 'cannot
group that selection' error. When I went back and deleted such
lines, the program allowed the grouping (dunno why).

Now, the resulting Pivot Table has a gray Sales Person at the top,
with a drop down arrow allowing the different reps to be viewed. Then,
a blank line and Sum of Charge in gray, Years in gray with another
drop down arrow for the various years, and a gray Date box with the
months January thru December available (and a check mark in each).
Next, a blank line and Customer Name in gray, again with a drop down
arrow and every single customer with a check mark. The customers are
grouped in the far left column, months Jan-Dec in the top row of the
actual spreadsheet, sitting below the Sales Person, Sum of Charge,
Years and Date section. Total dollar amounts are shown in the month
boxes as one views the resulting spreadsheet across and down the
pages - cool!

So, the Pivot Table is created nicely, but I would like to be able to
combine the new sales, new customers, new dates, etc. as time
progresses. That is, make a new .csv file with the current and future
months' data, then have the old Pivot Table suck up the new stuff.

Robert
 
Debra Dalgleish has an example of a dynamic range (a defined name that grows
when you add/delete rows).
http://www.contextures.com/xlNames01.html#Dynamic

Then you can just import your .csv file and copy|paste at the bottom of the
existing data.

The only time I've seen excel balk at not being able to group is when my data
didn't contain numbers/dates. Next time it happens, can you go back to the data
and see if that offending cell really is a number:

=isnumber(a1)
=len(a1)
may help find the problem.

Chip Pearson has an addin that allows you to determine what's in each character:
http://www.cpearson.com/excel/CellView.htm
 
Robert

Dave has pointed you to Debra's web site, which is realistically getting to
my original answer of creating a named range, which in seconds can be
updated each time and then, with you pivot table built on this, will
automatically update with the new data. We'll take it one step at a time

1) Import Data and delete anything not needed, (Blanks, etc)
2) Go to Insert>Name>Define...
3) In the Define Name box type a name (I usually use Database, Excel likes
that, but can't have spaces.
4) In the 'Refers to' box, click the RefEdit control (The button to the
right of the box)
5) Select the full extent of you data and click the RefEdit again to return
to the dialog
6) Click 'Add' and 'OK'

You have now named that range.

7) Start setting up you pivot table (Data>Pivot Table & Pivot Chart Report)
8) In the first wizard screen, select 'Microsoft Excel List or Database'
9) In the second screen, in the 'Range' box, type the name of the range you
set up in 1 - 6
10) Complete the wizard and adjust the layout to the way you want.
11) Next month, Import the data and repeat steps 1 - 6. (This time you range
name will be there so you just adjust the range in the 'refers to' box and
select Add/OK
12) Refresh the data in your pivot table from the pivot table toolbar (!)
and voila!

Hope this works for you
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Debra

Yes sorry, I looked at that but the OP was struggling with naming ranges at
all, so I thought I would point out a 1,2 3 first so that he could then
elevate to your solution.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick Hodge said:
Debra

Yes sorry, I looked at that but the OP was struggling with naming ranges at
all, so I thought I would point out a 1,2 3 first so that he could then
elevate to your solution.
Click to expand...

You are correct. I will give your outline a try. You were also
correct about the Grouping problem. When I examined the
original CSV file, I found there were some 'holes', where the
customer name or the sales rep name failed to be included in
the report. Once I entered values for those fields manually,
everything worked fine.

One last question - how does one 'undo' the Pivot Report and
return the spreadsheet to its original form? Before having created
the Pivot Table, I mean.

Robert
 
The pivot table, by default, is created on a new sheet in the workbook.
If you click the sheet tab where your original data was downloaded, you
should see it in its original condition.
 
Back
Top