Solved

Problem with Pivots

Posted on 2014-04-28
14
128 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now