Pivot Table not refreshing all of my information

Co315

New Member
Joined
Aug 20, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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 Balance0-2021-3031-6061-9090+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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,644
Office Version
  1. 365
Platform
  1. Windows
Are you changing the range of the source data? It wont update the range automatically unless its a table.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,242
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 20, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 23, 2021
Messages
2,242
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Forum statistics

Threads
1,147,477
Messages
5,741,362
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top