Solved

Pivot table refreshes only certain range of data

Posted on 2011-09-21
3
231 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 42
highlight duplicate entry 16 31
sql server query from excel 3 57
Pivot help - Display only Is Not Null 7 17
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now