Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Pivot table question

Posted on 2011-02-23
4
Medium Priority
?
193 Views
Last Modified: 2012-05-11
I have a workbook that has two worksheets.  One is labeled 'Pivot' and the other is labeled 'raw data'.  Pretty basic, the pivot table pulls the raw data.

What I would like to know if there is a way for me to use this over and over again (and have others use it as well) WITHOUT having to modify the range of the data each and every time the raw data is updated.

In other words, we use this every couple of weeks, or maybe once a month.  Basically we blow the data out that is on the raw data tab and paste new data, which is formatted exactly the same, into it's place.  However, the row count may vary. thus we have to adjust the range every time.

Is there not a way that we can paste new data and refresh the pivot table without having to adjust the range each tiime?   Thanks.
0
Comment
Question by:snyperj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 34960365
I may suggest to specify the maximum rows that you may have in your range. In such a way, when the count of rows increased the range is not affected.

Sincerely,
Ed
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 34960717
snyperj,

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 discusses a few options for doing this.

In Excel 2007 or later, using a Table as the data source makes the most sense.

In Excel 2003 or earlier, a dynamic named range is best.

Let me know if you need any help implementing these strategies.

Patrick
0
 

Author Closing Comment

by:snyperj
ID: 34960813
Your article appears to address all of the issues.  Well done.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34960865
snyperj,

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

636 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