Formula getting changed for dynamic pivot

[Umesh Chahar]

Hello everyone,
Hi have created a pivot and now i am trying to add more data or you can say making it dyanmic so that i need not change the range every time when new data is added to it .
so for to make it dyanmic i am using a offset function in change data source option ,its works fine only for first time when i am adding more data and refreshing it it does not work and if try to check the formula in the change data source the offset formula is not there .
the actual challenge is that i am able to use dynamic pivot only once with the help of function but if i add more data and try to refresh, the pivot does not show the new added data.
to make it more clear i am attaching the two pictures in two steps

 

[kchiba]

Hi Umesh,

Convert your Data into a Table, and make the Table the source.

Kanti

 

[Umesh Chahar]

Hi Kanti,
the data is into a backend and we are not suppposed to change the format /struture of data as there are so many other activites associated with the same data which will disturb the others environment, so there is not other choice using a change data source with offset function.

 

[jeffreyweir]

Add a new named range using the Name Manager with that formula, give it a name like 'Data' and then point the PivotTable at the 'Data' name.

 

[jeffreyweir]

the data is into a backend and we are not suppposed to change the format /struture of data as there are so many other activites associated with the same data which will disturb the others environment, so there is not other choice using a change data source with offset function.

Are you saying the data is in a completely different workbook?