Solved

Pivot table refreshes only certain range of data

Posted on 2011-09-21
3
234 Views
Last Modified: 2012-05-12
Hi,

I have never used a pivot table  before but need to get this report refreshed. Pivot table is in  (Excellworkbook1).sheetA
The pivot table is pulling data from a different spreadsheet (Excellworkbook2).sheetB
  There at 1430000 plus rows in this sheet (Excellworkbook2).sheetB.
. When I refresh the on the pivot table it works fine and the data is displayed correctly.

Now I have added some more data to (Excellworkbook2).sheetB   165000 rows. Whne i hit refresh the pivot table gets refreshed but it pulls data only for 142000 rows. How do I make it pull all the data in  (Excellworkbook2).sheetB.


It just pulls 142000 rows.

Can anyone please help.

Thanks

YRKS
0
Comment
Question by:SMadhavi
  • 2
3 Comments
 
LVL 2

Accepted Solution

by:
jan24 earned 250 total points
ID: 36577333
On the ribbon look under PivotData Tools / Options and click Change Data Source.  Make sure that the reference given includes the new rows you've added.

If you want to avoid having to do this every time then consider either/or:
1) Including all the blank rows to the bottom of the sheet
2) Create a range name in the source workbook, and refer to that as the Data Source for the pivottable
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 36577409
Providing more detail:

1) In file2, create a dynamic named range, PivotRng, referring to something like this:

=Sheet1!$A$1:INDEX(Sheet1!$1:$1048576,MATCH("ZZZZZZZ",Sheet1!$A:$A),MATCH("ZZZZZZ",Sheet1!$1:$1))

That assumes that your first column of data is using strings.  If numbers or dates:

=Sheet1!$A$1:INDEX(Sheet1!$1:$1048576,MATCH(10^200,Sheet1!$A:$A),MATCH("ZZZZZZ",Sheet1!$1:$1))

2) When creating your PivotTable, indicate this as the data source:

File2.xlsx!PivotRng

You might also want to have a look at my article on dynamic PivotTables:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36712954
SMadhavi,

Glad to help!  If you have not already done so, I would really appreciate it if you could please return to my article
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
and click 'Yes' for the 'Was this helpful?' voting.

Patrick
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question