October 14, 2015
I created a pivot table with an Offset formula in the range definition to allow for additional data to be added. It's not working properly and I'd like to see that formula again to confirm its accuracy. Is this possible?
Thanks for your help!
VIP
April 21, 2015
VIP
Trusted Members
June 25, 2016
July 16, 2010
I agree with Sunny. Definintely use Excel Tables for your source data. Here is a tutorial on Excel Tables as they have many benefits.
Mynda
October 14, 2015
I tried using a Table, but each month's data has to overwrite the prior month, which the table wouldn't let me do. I then tried using an Offset function to enter a dynamic range into the initial "create PT" dialog box which didn't work either. However, when I created a named range with the Offset function, then entered that name into the "Create PT" dialog box, it seems to work.
Thanks for the suggestions and the reference to the Table tutorial. I'm weak on structured references and appreciate the info.
VIP
April 21, 2015
Hm. Overwriting should not be a problem.
You can try first deleting the data that isn't proper anymore and after that putting in the new data (and after that of course refresh your pivot table).
Other way: first make the table for the new month (I think it's something imported?) on another tab and after that try to copy that data to your table (or first delete the data there etc.).
This is normally possible!
July 16, 2010
I'm with Frans; overwriting shouldn't be a problem.
Another options is to just use a filter on the PivotTable to choose the month's data you're interested in seeing. If you want to see the 'current month' then you can add a column to your source data that classifies the rows into 'current month' and 'prior months' and use this field in your filter with 'current month' selected. That way you don't have to change the filter selection.
VIP
Trusted Members
June 25, 2016
Since you need to override your data you should use what Frans suggested i.e. create a new sheet with an empty Table every month.
I use both overriding and deleting data method and did not encounter any problem at all.
It would be best if you could post a sample file for us to see what you have. It is difficult for us to guess the structure of your table.
1 Guest(s)