Solved

Dynamic Pivot table source data

Posted on 2015-01-20
4
83 Views
Last Modified: 2015-01-20
Hi,

I want to use a dynamic named range for a pivot table so that when I update the data I will not have to update the range.  
The current data range is 'Worksheet'!$A$1:$AC$74338, but this could of course change as more data is added or removed.

I appreciate the help,
Montrof
0
Comment
Question by:montrof
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:slubek
ID: 40560724
Create your range as a table and base your pivot on that table. Every change (insert/delete) in table rows will cause pivot recalculation.
0
 
LVL 1

Author Comment

by:montrof
ID: 40560729
would it not be better to use a dynamic range because on the data tabs there is some other stuff that could get affected if I make it a table
0
 
LVL 7

Accepted Solution

by:
slubek earned 500 total points
ID: 40560764
If the table is a problem, create dynamic named range, for example referring to something like
=OFFSET($A$1,0,0,COUNTA($AC:$AC),1)

Open in new window

and use that dnr as a source of your pivot.

I suggested table because it worked for me, and I've never tried to use dnr as a pivot's source. I'm not sure, but if calculations of COUNTA() are done every time you use that dnr, then (with so many rows that you have) it could lead to performance issues. You have to test it.
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 40560769
Thanks I will play with both options.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

792 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