Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

Dynamic Pivot table source data

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
montrof
Asked:
montrof
  • 2
  • 2
1 Solution
 
slubekCommented:
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
 
montrofAuthor Commented:
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
 
slubekCommented:
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
 
montrofAuthor Commented:
Thanks I will play with both options.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now