Pivot Table not refreshing all of my information

[Co315]

I have a weekly Pivot Table that I have to update for my manager. This is an Accounts Receivable aging report. The workbook contains 3 worksheets for "AR Over 90 days", "AR 61-90 days" and "AR 31-60 Days". I have a worksheet for the data table that I get from our accounting program. The source data has the customer name, Sales person, Sales order #, and a column for "Aged Days'

Invoice Balance

0-20

21-30

31-60

61-90

90+

Aged Days

I inserted the headings of some of the items so you would get the picture. I've created the pivot reports so the it pulls the Customer name, Sales person, Sales order, Aged Days and Balance. Every week I pull a new AR aging file from out accounting program and I replace the data source info from the prior week with the new week's data and then I do an update for each of my 3 pivot tables. It works great EXCEPT when this happens. Occasionally, a customer will make a payment or a deduction on an old invoice so it will "be opened back up" in our receivable report. Normally it will appear in our over 90 column in the source data worksheet. So even though the previous week this did not not show on the "over 90" entry the new data will have it and when I try to refresh the over 90 pivot table, it will not add this individual item. All of my items from the previous week will still be there if they haven't been paid, and any new items that hit the over 90 report will show up when I refresh the report, but for some reason any "old items" that have been reinstated will not refresh. I hope this makes sense. I can even click on the filter of the Aged days and see the filter of the aged days but there is no choice for the new aged days of this new entry for the over 90. I may have to send a sample of the spreadsheet. It seems pretty basic so I'm hoping I need to to attached a spreadsheet but will if I need to. Does it have anything to do with the Pivot Cache from the Previous Week? And one more point. Every week I take the previous week's report and do a "save as" with the new data. I then overwrite the previous week source data with the new source data. I wasn't sure if this piece was relevant. Thank you in advance for your help!

 

[steve the fish]

Are you changing the range of the source data? It wont update the range automatically unless its a table.

 

[Alex Blakenburg]

I can even click on the filter of the Aged days and see the filter of the aged days but there is no choice for the new aged days of this new entry for the over 90

Is you filter based on a column that has a formula in it ?
Can you filter the source table to find one of the missing transactions and see if the column used for the filter makes sense.

 

[Co315]

Are you changing the range of the source data? It wont update the range automatically unless its a table.

Yes, I did create the source data as a table. It doesn't not make since. The only way for to get it added to my pivot table is to add it manually at the bottom of pivot table, or delete the worksheet and create another pivot table. I feel like it's something simple that I'm not doing, but I can't figure it out! lol

 

[Alex Blakenburg]

If you go into the Pivot Table's Change Data Source, does it definitely have the table name in the source box and that name matches the name of the table your are expecting it to use ?