need help with pivot tables

[jpurl77]

i'll try to explain the best i can here:
i have 3 sheets, 1. table with my quantity info, 2. (source data) table
1 transposed, 3. many "mini tables" referencing sheet 2
i first setup my pivot tables and got the "mini tables" setup the way i
needed. then of course quantities changed, then after updating sheet 1
and copying a transposed version to the second sheet and deleting and
adding rows and columns (so the headers will work for pivot table) data
dissappears from my pivot tables. what am i doing wrong? is there a
better procedure to accomplish what i need.
thanks ahead of time for any info
jeff
(e-mail address removed)

 

[KC Rippstein]

Sheet2 should not be a manual process. If you can make it a reference to
Sheet1 with the sorting, formatting, and limitations you want, then your
pivot table won't get confused because of inserting/deleting columns and
headers.

If Sheet1 has all your data, and Sheet2 is a stripped down and organized
"summary" version of Sheet1, just use some lookup or index/match formulas to
set up Sheet2. Then Sheet2 will never be manually touched again and your
pivot tables won't get confused.

Honestly, you should be able to use the pivot table functionality without
that middle step of a Sheet2. You can tell the pivot table exactly what
headings and rows to use. Pivots are almost always done off of the raw
data, not off of summary, stripped down data. Heck, the whole point of the
pivot table is to give you a summary, stripped down version of huge amounts
of raw data.

I have a large database import I run monthly that I apply one filter to
(remove all records with zero dollar values) and then I copy the dataset
into Sheet1 of a workbook template I made. Sheet2 is automatically set up
to run a pivot table off of columns A through G from Sheet1 and then I have
Sheet3 compare each client budget from another workbook to the actual
revenue we've received YTD according to the pivot table. I just use a
simple vlookup to accomplish that.

Keep things as automated and flexible as possible and you shouldn't have any
troubles. If I had my pivot table run off of Sheet1!A1:G10000 and come to
December to find we have 12,000 revenue entries, my pivot table data will be
wrong. This may or may not answer your question, but from the sound of it
you need to think through your design process a bit on this project.