Name a range of data with variable

  • Thread starter Thread starter Grey
  • Start date Start date
G

Grey

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
 
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
 
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.
 
Back
Top