Solved

Pivot table refreshes only certain range of data

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

823 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