Maybe you can provide an example file? There are more things to know why this doesn't work properly. Depends also on where and how you used your Offset formula.

Have you considered using a Table for the data? The range will auto expand when new data is added.No formulas involved.

Then you only need to refresh your PivotTable.

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

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.

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!

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.

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.