[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Pivot table refreshes only certain range of data

Posted on 2011-09-21
3
Medium Priority
?
241 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 1000 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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 1000 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 93

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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

612 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