Solved

Problem with Pivots

Posted on 2014-04-28
14
131 Views
Last Modified: 2014-05-02
is there a way to prevent the 'Data Source' range from changing when data is changed.

I have set up a pivot with a data range called RawData. The range for this is ='Raw Data'!$A$7:$AL$10000

However, if tomorrow the total rows are 5000 the pivot will change itself to 5000 which means the following day when there are 8000 rows it will stay at 5000 and my pivot will be incorrect.

Here's hoping.
Thanks
0
Comment
Question by:Jagwarman
  • 7
  • 7
14 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40027135
That shouldn't happen unless you update the data by actually deleting rows rather than clearing them.

In any event you would be better off using a dynamic named range or a Table which will both expand and contract as required. See Debra's page here for dynamic named ranges: http://www.contextures.com/xlPivot01.html

and note the link to this page for creating a Table: http://www.contextures.com/xlExcelTable01.html

If you have 2007 or later I would recommend a Table.
0
 

Author Comment

by:Jagwarman
ID: 40027211
Hi Rory,

I am using a table.  ='RawData'!$A$7:$AL$10000

So the Table is called RawData and the range of the table is A7:AL10000.

But when new data is pasted onto the Raw Data sheet the range changes. ????
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40027278
If you are in fact using a Table then you don't enter the range address in the Source Data for the pivot, you just enter the table name = RawData, and then you should only have to refresh the pivot after updating the data. If that's not working, then I don't think you are using a Table.
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Author Comment

by:Jagwarman
ID: 40028922
Hi Rory,

I am using a Table but somthing is wrong.

I set up the Table in Excel 2010 through 'Insert/Table' The range is $A$7:$AL$10000

I called the table RawData and this is confirmed when I go into 'Design'

In the Pivot in Table/Range I have entered RawData

But, when I bring in a new file and paste this into the worksheet the Table Range has changed from 10000 to 1242 which is the total number of rows that there were yesterday. That's fine but when I bring in today's file which has 4000 rows the table does not adjust it remains at 1242

Appreciate your help with this.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40028982
How exactly do you bring in today's file with the extra rows - copy and paste?
0
 

Author Comment

by:Jagwarman
ID: 40029017
yes copy and Paste
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40029095
You don't delete anything first? How do you do it when the range is smaller than the current table?
0
 

Author Comment

by:Jagwarman
ID: 40029210
I copy the range A:AL
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40029645
In Excel options, in the Advanced section, do you have the setting for 'extend data range formats and formulas' checked?

Also, does your range still appear as a Table when you paste over the top of it (either less or more data)?
0
 

Author Comment

by:Jagwarman
ID: 40031643
Hi Rory,

In Excel options, in the Advanced section, do you have the setting for 'extend data range formats and formulas' checked?

Yes

Also, does your range still appear as a Table when you paste over the top of it (either less or more data)?  

It does but if I have brought in less data the previous day the Table range adjusts to the new lower range and when I bring in more data the next day it remains at the lower range.

i.e. origianal range A7:AL10000

less data changes to A7:AL4963

more data remains at A7:AL4963 despite there being 7300 rows
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 40034297
I confess I'm confused. If I have a table that starts in row 7, and I copy and paste entire columns from another sheet, the table is removed.
0
 

Author Comment

by:Jagwarman
ID: 40034485
so I guess the answer is to build into my macro the Range each time
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 40034739
Or try not copying entire columns - just copy the data and paste it below the table headers.
0
 

Author Comment

by:Jagwarman
ID: 40036515
Rory

it looks like that works.

Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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 use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

860 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