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'
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!
|Invoice Balance||0-20||21-30||31-60||61-90||90+||Aged Days|