Dear Phillips,

thanks for your quick reponse. But how I can connect this function to the
pivot table? I have followed the step InsertNamedefine and input the said
formula "=offset(data!$A$1,0,0counta(data!$1:$1))" on the data worksheet.

What will be next step I need to do so that the pivot table can be updated
automatically when new rows are added on the data worksheet.


thanks again.
--
vicki


"Bob Phillips" wrote:

Define a dynamic named range for the data and use that in the pivot table.
See http://www.contextures.com/xlNames01.html#Dynamic

--
__________________________________
HTH

Bob

"vicki" wrote in message
...
Happy New Year and all the best.

I have a pivot table which looks up a worksheet data who's range change
daily, i.e. new data are added every day. Is there a way to have the
pivot
table select all rows of data without having to change the data range
manually everyday?

many thanks.


--
vicki

(Assuming you defined your named range as MyRange)
Under you pivot table options, where it has you select a range of cells, type
=MyRange

The Pivot Table now looks to those cells, which based on the OFFSET formula,
will adapt as needed. Again, change "MyRange" to whatever you decided to call
your Range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"vicki" wrote:

Dear Phillips,

thanks for your quick reponse. But how I can connect this function to the
pivot table? I have followed the step InsertNamedefine and input the said
formula "=offset(data!$A$1,0,0counta(data!$1:$1))" on the data worksheet.

What will be next step I need to do so that the pivot table can be updated
automatically when new rows are added on the data worksheet.


thanks again.
--
vicki


"Bob Phillips" wrote:

Define a dynamic named range for the data and use that in the pivot table.
See http://www.contextures.com/xlNames01.html#Dynamic

--
__________________________________
HTH

Bob

"vicki" wrote in message
...
Happy New Year and all the best.

I have a pivot table which looks up a worksheet data who's range change
daily, i.e. new data are added every day. Is there a way to have the
pivot
table select all rows of data without having to change the data range
manually everyday?

many thanks.


--
vicki

Luke,

it works now, million thanks for your help.


--
vicki


"Luke M" wrote:

(Assuming you defined your named range as MyRange)
Under you pivot table options, where it has you select a range of cells, type
=MyRange

The Pivot Table now looks to those cells, which based on the OFFSET formula,
will adapt as needed. Again, change "MyRange" to whatever you decided to call
your Range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"vicki" wrote:

Dear Phillips,

thanks for your quick reponse. But how I can connect this function to the
pivot table? I have followed the step InsertNamedefine and input the said
formula "=offset(data!$A$1,0,0counta(data!$1:$1))" on the data worksheet.

What will be next step I need to do so that the pivot table can be updated
automatically when new rows are added on the data worksheet.


thanks again.
--
vicki


"Bob Phillips" wrote:

Define a dynamic named range for the data and use that in the pivot table.
See http://www.contextures.com/xlNames01.html#Dynamic

--
__________________________________
HTH

Bob

"vicki" wrote in message
...
Happy New Year and all the best.

I have a pivot table which looks up a worksheet data who's range change
daily, i.e. new data are added every day. Is there a way to have the
pivot
table select all rows of data without having to change the data range
manually everyday?

many thanks.


--
vicki