how can i include new add data on my pivot table
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 |
how can i include new add data on my pivot table
(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 |
how can i include new add data on my pivot table
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 |
All times are GMT +1. The time now is 11:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com