Name a range of data with variable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Grey - 04 Oct 2004 07:44 GMT

How to name a range of data with variable?? I want this range of data would
be expand when I add a new row of data from time to time, but I cannot
include the newly created one with the variable

Reply to this Message

Frank Kabel - 04 Oct 2004 08:43 GMT

Hi
use 'Insert - Name - Define' and the following formula in the 'Refer
to' box:
=OFFSET($A$1,0,0,COUNTA($A:$A))

to create a dynamic name

Signature

--
Regards
Frank Kabel
Frankfurt, Germany

> How to name a range of data with variable?? I want this range of data
> would be expand when I add a new row of data from time to time, but I
> cannot include the newly created one with the variable

Reply to this Message

Dave Peterson - 04 Oct 2004 21:38 GMT

Just to add to Frank's reply:

If you always have data in column A and row 1 (no gaps at all!), you can use a
refers to formula for your name like this:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Then you can just rightclick and refresh your pivottable.

And just to test to see if you defined the name correctly,
Edit|goto|type in the name you used and hit enter

That's what the pivottable will use.

> How to name a range of data with variable?? I want this range of data would
> be expand when I add a new row of data from time to time, but I cannot
> include the newly created one with the variable

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message