Pivot Table - how to prevent Refresh overwriting rows below the Table

I have a Pivot Table based on data that gets imported.

The number of rows in the Pivot Data varies depending on the underying
data.

Below the Pivot Table are some calculations and then a couple more
Pivot Tables.

I'm building a Profit & Loss report and each Pivot table summarises the
relevant report sections (e.g. Sales Pivot Table, Cost of Sales Pivot
Table).

The problem is, when the pivot table refreshes, it can overwrite
formulas and tables below it if the table increases in rows.

Any ideas/suggestions please to prevent this.

Thanks

T Hunt

0
tim.hunt (4)
11/23/2005 2:02:37 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1080 Views

Similar Articles

[PageSpeed] 36

I don't know if you can prevent that with refreshed pivot tables.  There is
more control in this regard when refreshing an external data query.

However, I suggest staggering your steps using sheets.  If you create
separate sheets for each pivot table and/or data query, then have a separate
sheet again for the final report page that puts them and formats them
altogether.  The cells in that sheet can have absolute references to the
cells in the other sheets so that whatever they get updated with would
appear in the final report.  You'd have to make the format accommodate
different amounts of rows to scale to your pivot table sizes.

Hope this gets you on a workable track.

"thunt" <tim.hunt@computersoftware.com> wrote in message
news:1132754557.922866.15120@g47g2000cwa.googlegroups.com...
> I have a Pivot Table based on data that gets imported.
>
> The number of rows in the Pivot Data varies depending on the underying
> data.
>
> Below the Pivot Table are some calculations and then a couple more
> Pivot Tables.
>
> I'm building a Profit & Loss report and each Pivot table summarises the
> relevant report sections (e.g. Sales Pivot Table, Cost of Sales Pivot
> Table).
>
> The problem is, when the pivot table refreshes, it can overwrite
> formulas and tables below it if the table increases in rows.
>
> Any ideas/suggestions please to prevent this.
>
> Thanks
>
> T Hunt
>


0
hall (126)
11/23/2005 2:33:46 PM
Thanks, I've tried that approach, but still run into problems when
trying to bring everything together on one worksheet due to the
variable number of rows.

0
tim.hunt (4)
11/24/2005 9:14:48 AM
Hi Tim

I bring all my data from Sales Ledger and Purchase Ledger together into one 
sheet, and do a PT on that.
My report is on a separate sheet altogether, and uses the GetPivotData 
function based on the column Name (month) and row name (Nominal Code).
That way, it doesn't matter where in the row range the Nominal code is, it 
is found by GetPivotData.

Regards

Roger Govier


thunt wrote:
> Thanks, I've tried that approach, but still run into problems when
> trying to bring everything together on one worksheet due to the
> variable number of rows.
> 
0
roger1272 (620)
11/24/2005 1:18:08 PM
Thanks Roger,

I think I've tried that, but without success so must be doing it
differently.

When you assmble the data on the separate sheet, is it working to a
fixed list of nominal accounts?  That would work for me, but what I'm
trying to accomodate is automatically including on the report any
additional nominal accounts that may occur.

0
tim.hunt (4)
11/24/2005 2:59:44 PM
Hi Tim

Trying to hit an ever moving target, eh!!!
No, in the case I refer to the Nominal code list is fixed. If there are 
further additions, then the report has to be modified to pick those up.

The client concerned would post additional items to a Suspense account 
(included in the report). On the next routine visit (usually quarterly), I 
would agree with them the addition of the new nominals, and journal from 
suspense to the appropriate nominal.

Regards

Roger Govier


thunt wrote:
> Thanks Roger,
> 
> I think I've tried that, but without success so must be doing it
> differently.
> 
> When you assmble the data on the separate sheet, is it working to a
> fixed list of nominal accounts?  That would work for me, but what I'm
> trying to accomodate is automatically including on the report any
> additional nominal accounts that may occur.
> 
0
roger1272 (620)
11/24/2005 3:10:52 PM
Reply:

Similar Artilces:

refresh pivot report
Every time I refresh my pivot table I lose formatting that I have to re-do every time, specifically column widths, cell borders and cell formatting such as bold and wrapped. I also lose this formatting when I use my page fields drop down box to look at different groups of data. Is there anything I can do to retain my formatting in these situations? -- Julie Julie If you go into the Pivot table options you can de-select the box "Auto Format Table". This should do the trick. Roger "Julie" wrote: > Every time I refresh my pivot table I lose formatting that I...

Pivot tables remember values that are not available anymore
Hello everyone I would like to ask you is it is possible to reset the fields in a pivot table to make disapear values that they are not anymore available on the data form the selection-criteria when a fields is drill down example lets make a simple table like :: uno dos tres a q 1 s q 2 d q 3 f q 4 g q 5 a w 6 s w 7 d w 8 f w 9 g w 0 make a pivot table and drilldown "uno" (row fields), drillacross "dos" (column fields) and "tres" as Data-Items so in the selection criteria for "uno" ...

Reference every 4th row from a master list of modifiable length
I'm creating an excel database for a clinical study on patients wit migraine. Each patients will treat 4 attacks (episodes). Thi presents a bit of a problem, since there's some information I' collecting that's specific to the patient (demographic information lik sex, height, etc.), and some that is specific to each attack (like th pain level of that attack, the duration of that attack, etc.). T account for this, I decided to give each patient four rows--one fo each attack--and I put their deomographic information in the secon row, leaving the other three blank in those fiel...

How do I not print the column letters A-Z but keep the row #'s
I am trying to print a chart where I want to keep the row numbers but not the column headings A-Z printed. Choosing Page setup under the Sheets tab only lists them together not as single choices. Is there a way to customize the sheet to only list the row numbers? How about inserting a new A column and adding =ROW() best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jgb" <jgb@discussions.microsoft.com> wrote in message news:E503B439-3323-4CE9-A5B8-5477CFC091C2@microsoft.com... >I am trying to print a chart where I want to keep the row ...

Calculated items/fields in pivot table
Dear all, I am new to excel pivot table and I would like to ask you all a question regarding the calculated items/fields in pivot table. I am making a pivot table whose data source is shown below: Team Salesperson Amount Sold Price each Team Person Amount Sold Price each Paper A 50 35 Paper D 60 25 Pen D 500 4 Desk C 20 400 Desk A 10 700 Pen B 700 3.5 Clip B 500 2.5 Clip C 600 2 Desk B 15 600 I am thinking if it is a way to add a field in the pivot table which can show the total sale of each Team. For e...

A/P Tables
Hi, Which table(s) contain(s) the A/P checks/payments? Which column hold the check number? Are payments via credit-card in the same table(s) with checks? Many thanks, -Itai You can find this information in the SDK found on the second Great Plains cd. Payments are payments. The type is a column in the table. "Itai" wrote: > Hi, > Which table(s) contain(s) the A/P checks/payments? > Which column hold the check number? > Are payments via credit-card in the same table(s) with checks? > Many thanks, > -Itai > > > > > Charles, The SDK indic...

how to create a chart from a dynamic table of data?
hai!! i need to create a chart from a dynamic table,meaning that the table is auto-generated from database after clicking a button.This is my first time using macro in excel, so i'm not really well in it. my idea right now is using Chart.Add,but then i'm having problem on how to define the cells selection(i must do "the do..loop statement" rite?) ur help is very appreciated,tq! Use the macro recorder as a guide. When I record a macro to create a simple chart it looks like: Sub Macro1() ' Macro recorded 7/30/2007 by Jon Peltier ' Charts.Add ActiveChart.Ch...

Hiding Rows Conditionally
Is it possible to hide a row only if a certain cell in the row contains a reference to another cell that has a specific value? Here's the scenario: Column A of a Summary sheet contains a list of 20 categories. Each category row contains references to data on another worksheet. Often, there is no data for a specific category, so the Summary sheet displays the category with zeroes or various errors depending on the formulas referencing the data. I would like to hide the entire row if the referenced data cell contains a certain value, for instance "0". Can it be done? Thanks for a...

Combining rows of data that have one cell in common
Hi everyone, let me explain... I'm working with a large excel file (13,000+ rows) of cancer cases recorded in the state of Florida. Each time a patient presents with a cancer, that "case" is recorded as a row on the spreadsheet, along with a UNIQUE patient ID assigned to that particular individual. If that same person develops another cancer sometime later, that information appears in a new case/row, but with the SAME patient ID. I need to export this file to another program (SPSS v13.0) to do some advanced statistical analysis. However, the program requires that all of t...

Pictures don't display in tables
Have been inserting jpegs into word tables for years. Suddenly the picture doesn't display in normal or print mode. Instead I get an border. If I click inside of the border the drag handles appear but they are not active. It's a pain I can live with as the pictures do print however, I can't size them. What have I messed up? -- Joe Hi Joe, You might want to check if you have the option "Show picture placeholders" enabled? Its help text indicates "Display an empty box in place of each graphic in the document to improve scrolling performance....

vb dataset row count incorrect
Hi Has anyone else had this problem. Im using a vb .net dataset to read & write XML but when I check the number of rows for a table it's incorrect. Example number of rows = 4 table row count says there are 8 rows does anyone know how to correct this problem ? Regards Neil neil rowe wrote: > Has anyone else had this problem. Im using a vb .net dataset to read & > write XML but when I check the number of rows for a table it's incorrect. > > Example number of rows = 4 > table row count says there are 8 rows That may have something to do with whitespa...

Printing the first two rows as headers.
I would like to have the first two rows print at the top of every page of my print out. I know this is a basic function, yet I can not seem to find it in the help function. Could someone please post how to do this /AND/ tell me what key words to put in the search/help function that would show the answer as well. Thanks Michael -- J. Michael Morse michael@stignatius.cc <mailto:michael@stignatius.cc> St. Ignatius <C:/Parish%20Administration/Graphics/Bulletin/Ignatius2C.jpg> "Let no one ever come to you without leaving better and happier." - Mother Teresa Hi ...

Unable to insert record in table
Hi, Using SQL Server 2005. I have a table with one required field (key field, var type int) and various other optional fields. Yesterday, my Access VBA code failed to action the following SQL statement: INSERT INTO ORDERS (OrderID) VALUES (9121165) I got a time out error and a report that the record could not be inserted (error code -2147217887). So I opened SQL Server Managment studio and opened the table. When I tried to insert a new record using the same OrderId, I first got a timeout error and could not add the record. AFter OK'ing the error, I was able to cli...

Looking up data from a table conditionally
Hello, I would like to know how to create a formula that looks at a value in one cell, uses that value to find a corresponding value in a separate table, and then enters that value in my sheet. For example, say I want a value to appear in cell B1 based on the value in cell A1. If the value in cell A1 is "B", and the value in a separate table that corresponds to "B" is $500, I want the formula in cell B1 to return the value "$500". Any ideas on how to do this? Thanks, Eric Classic application for the VLOOKUP formula......... =VLOOKUP(A1,YOURTABLE,2,FALS...

Pivot Table
In my Pivot Table, I have all the column headers formatted to "wrap text". When I select new data to display in the pivot table, it removes the wrapping on the column headers. I've deselected the AutoFormat Table option, but the behavior continues. Any way around this? You may have to enable selection (From the Pivot toolbar, choose PivotTable>Select, and click on Enable Selection) Before formatting cells, instead of selecting the entire column, use the selection feature to select the items in the description field. For example, move the pointer to the top of a column ...

Need Records from 2 Tables
I have an Employee table in Access that is updated weekly from a Payroll system. If someone is terminated, they will no longer be in this table. It was suggested I create a "History" table to keep from losing these terminated records. BUT, this would mean when I query to find an associate, I would need to look at all the records from the Employee Table, and just the unmatched records in the "History" table. With what I know of joins, there isn't a way to do this. So what do I do? Any help would be greatly appreciated!! PB, If you move the terminated empl...

Re: Preventing Appointments being made
Hi New to this group and have read a little but not found what I am looking for. Is it possible within Outlook 2000 to set up a series of day when someone is on holiday so that no appointment can be added by mistake. I know a message can be viewed at the top of the calendar when a series is set as All day and Out of Office but this doesn't seem to prevent an appointment being made by mistake. Further to the above I would need to do the same with bank holidays. Also, is there a way to make the bank holidays stand out in, say, another colour? Any help appreciated. Cheers Go into t...

Pivot table from Multiple Consolidation Ranges
Dear All Excel Experts, Creating a Pivot Table from 1 data table in 1 worksheet gives me the Pivot Table the way I want it, meaning as many Page Fields as I defined them at the top left of the Pivot Table. These Page Fields represent the column lables and in the drop-down I can choose the data under that lable of the respective column. Now I wish to have precise the same Pivot Table, but this time from data tables spread over several worksheets, whereby the format of these data tables are exactly the same. But now the drop-down of the Page Fields at the top left of the Pivot Table g...

How to anchor data label show value option on Pivot Chart
I have a pivot chart and I like to show the data value above the column, however when I resort the chart with a new query, the data labels disappear and I have to go back through format data series/data labels/show value all over again. How do I get the data labels to stay and update automatically based on the new sort Leroy This is an admitted deficiency in pivot tables and charts. Next time you have to redo a chart's formatting, record a macra, then play it back every time after that. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Sol...

How to Show/Hide Columns or Rows with no data via VB
Hi, I am need of some advice please. I have a spreedsheet (2007) which I designed as an attendence register. Currently it works well but I want to improve on a few matters. For the main page I have a column with the date, next column day, then the next 40 columns are set for the crew employees. These are broken into 4 groups of 10 for each crew (A,B,C,D, Shift workers), however there is normally only 7 people per crew, the other 3 are blank unless there are extra enployees to that crew. Currently I am hiding and showing manually, but I want to be able to do something via ...

Linked Table
Hi, I have a Access 2003 mdb that I have used for years that has worked OK. Recently one of my queries malfunctioned to an external Progress db's linked table. Specifically, the query's table list is missing many of the fields in the table though when I inspect the linked table in the Tables Objects Section I see all the fields. How can I fix my query? TIA, Dan Why post the same question in different newsgroups? (already answered in the other newsgroups you posted in). If your situation is one of the (few) unusual circumstances in which it is necessary/appropriat...

Table Definitions #2
Where can I find the table layout and definitions of the object items in each table. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=7e5ff417-b4b2-49b3-8a3b-e43117c11a2c&am...

QTY Shipped in Purchasing Line Receipts Table
Does anyone know why the quantity fields in this table have a "currency" storage type in table POP10500? The field type in the SQL table is numeric. I want to do a Smart List showing receipt transactions and the data comes out in currency format in my list. Can this be changed in the database or is there some obscure reason why it's coded this way? If so, where would it be changed? Thanks, Frank Hamelly NOVA Solutions LLC It is a currency field for 2 reasons: 1. It provides QTY's greater than 2.1 billion (limit for long integer). Probably not a real good reason as I&...

Select Statement in query to append to a table
Hello, I have a table with the following Master Stock Code Header ID and I want to append this information to a table, kind of ike a cross tab query but I want the value of the header ID to be in the column not as a column name. I have a max of 12 header id's, so I have 12 column names in the table i want to append to (ie. Hdr1, Hdr2 etc) There could be more than one Header ID per stock code. WHat is the proper syntax for the select statement in my query so I don't get duplicate rows per stock code. ...

Conditional Formatting Whole Row
Morning All, Using Windows and Excel XP. I have a worksheet that has the dates of this month in A1:A31 and other data in B1:F31. I have set up a conditonal format: cell value equal to =TODAY() to have the current date (December 6) in A1:A31 to highlight in yellow. What I would like to do is not only highlight the current date but also all the data in that particular row. For example, in A1:A31, December 6, 2003 (cell A6) is highlighted in yellow. How can I get the entire row (A6:F6) to be highlighted in yellow. Is it possible to do that? Thanks in advance, Mi...