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
LVL 1
montrofAsked:
Who is Participating?
 
slubekConnect With a Mentor Commented:
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
 
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
 
montrofAuthor Commented:
Thanks I will play with both options.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.