Dynamic Pivot Table source
 

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

Dynamic Pivot Table source
 

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,
--
Farhad Hodjat


"Conan Kelly" wrote:

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

Dynamic Pivot Table source
 

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




"Farhad" wrote in message
...
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,
--
Farhad Hodjat


"Conan Kelly" wrote:

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

Dynamic Pivot Table source
 

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.

--
Regards
Roger Govier



"Farhad" wrote in message
...
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,
--
Farhad Hodjat


"Conan Kelly" wrote:

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