dynamic data source

[chaz]

I have a source datasheet that annually grows by one
column, but the number of rows are fixed. This datasheet
populates a pivot table. I would like to create a dynamic
data range based on source sheet so that I only have to
refresh the pivot table, rather than redefine it through
the wizard. I received help with a similar topic from
contextures.com based on offset and counta which worked
when the columns were fixed but the number of rows
increased...

More broadly, is there a way to create a dynamic range
that would allow both the number of columns and rows read
by a pivot table updated automatically?

thanks in advance,
chaz

 

[Andy Brown]

You just have to rejig Debra's formula slightly, eg:

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

Or even

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

Rgds,
Andy

 

[Aladin Akyurek]

Let the data area start in A2 on Sheet1.

Activate Insert|Name|Define.
Enter BigNum as name in the Names in Workbook box.
Enter the following in the Refers to box:

9.99999999999999E+307

Click Add.

Enter BigStr as name in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click Add.

Enter Pdata as name in the Names in Workbook box.
Enter the following in the Refers to box:

=OFFSET(Sheet1!$A$2,0,0,MATCH(BigNum,Sheet1!$A:$A)-CELL("Row",Sheet1!$A$2)+1
,MATCH(BigStr,Sheet1!$2:$2))

Click OK.

The latter formula assumes that column A on Sheet1 is numeric (dates, for
example). If column A is text, replace BigNum with BigStr. The labels (which
Pivot Tables require) are in row 2 (hence Sheet1!$2:$2).