Dynamic Pivot Table source

[Conan Kelly]

Hello all,

I haven't tried this yet, but I was wondering, when creating a pivot table
based on data in the current workbook, if the source range can be dynamic
using the OFFSET() and COUNT(A)() functions.

Thanks for any help anyone can provide,

Conan Kelly

 

[Guest]

Hi,

Try this:

go to Insert> Name> Define

put the name you want in the name textbox (in the top) and put the formula
below in the Referto textbox:
=OFFSET($A$1,0,0,COUNTA(OFFSET($A$1,0,0,9999,1)),??)
assumed the first cell of first column on the left of your data is A1 and
you sould put the quantity of your data's columns instead "??" in the formula
above
click ok
this name can grow up to 9999 rows so if you want more you should change the
number 9999 to what ever you want.

Thanks,

 

[Conan Kelly]

Farhad,

Thanks for the info.

I read that somewhere before, but I keep forgetting that things can be done
that way.

Thanks again for all of your help,

Conan

 

[Roger Govier]

Hi

There is no need to give a row restriction, and you can count the columns as
well.
Try
=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

This assumes that your headers are in row 1, and that you do not have any
entries in row 1 beyond your table itself.