Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

Pivot table refreshes only certain range of data

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
SMadhavi
Asked:
SMadhavi
  • 2
2 Solutions
 
jan24Commented:
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
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now