Forum Discussion
Excel Pivot Table corruption after loading saved file
I don't know that this is a better answer, but I hope it's a better question: WHY do you go through that fairly elaborate process? This one: I perform a move copy function and move the pivot table to a new spread sheet and resave the file by "XXXX update" Today's date for distribution and then save the master file.
Why do you not just save the updated file? What's behind the move copy, moving the pivot table to a new spreadsheet....each of those steps? In some ways it doesn't surprise me that it occasionally breaks the link between the pivot table and its data source. What surprises me is that it seems to work some of the time.
But more seriously, one of the Pivot Table's features is that you can hit "Refresh" after updating the source data, and voila...there's your updated Pivot Table. If you're keeping history, and need to be able to go back and look at older dated Pivot Tables, that could be done by the date ranges....
I realize there may be entirely valid reasons for doing it as you are, but had to ask the question first.
On a separate but related tack: are you aware of the new Dynamic Array Functions? (FILTER, UNIQUE, SORT, etc)....they can be used to create a report quite similar to that produced by the Pivot Table, and don't need "refresh".....they will automatically reflect new data added to an Excel Table.
Master contains sensitive information which I do not intend to distribute along with the information in the pivot table. I may in the future sperate the two, but I have not had much luck with linking spreadsheets.
The pivot table essentially is a schedule rollup, i.e a delivery needs to occur on 5/16 with instructions and billing info. The formula that dictates the delivery date works off of an array of dates and TODAY() date:
=IFERROR(MIN((IF(A1="Cancelled","",IF(A1:G1>=TODAY(),A1:G1)))),"").
So when I update this for a certain week or month, it only pulls data from whatever TODAY() is and forward for the next closest delivery date. So if I needed to pull data for a month of deliveries today, I would have to change my computer clock to 5/1/2020 followed by refreshing the table. But if two deliveries are made in the same week/month it will not display the next delivery until it is refreshed after that particular date - i.e. delivery date for a particular location is on 5/11 and 5/17 - table wont display 5/17 delivery date until refreshed tomorrow.
I'm sure others may have a more elegant method....
I got a little bit off topic, but to answer your last question, I have not read up on these functions yet and will look into seeing if they may be applicable for my needs.
Move/copy in my opinion is the correct function to move over a copy of the pivot table as it maintains functionality of the pivot table for the intended recipients to look forward, unlike a simple copy/paste.
Finally, while I appreciate you bringing up the dynamic array functions, perhaps you might have an answer to why these links break? Is there a certain set of steps that cause this bug that you are aware of? As you mentioned, I have reasons for why I built it this way and I sure there are others who may have brilliant ideas on how to rebuild my formula's, but I am just trying to figure out why excel is killing my tables.
Thanks
You wrote: Master contains sensitive information which I do not intend to distribute along with the information in the pivot table. I may in the future sperate the two, but I have not had much luck with linking spreadsheets.
I wonder if the solution might be located here: concentrate on separating the Master sheet, with its sensitive data (apparently along with non-sensitive used by the Pivot Table) from the spreadsheet that contains the Pivot Table which is to be distributed without that sensitive data. Just draw into the distributable sheet the necessary data and refresh it.
My situation is not like yours, but let me describe it. It's unlike in that I don't have the crucial "distribute to others" of my secondary or downstream spreadsheet. But on a regular basis (usually daily) I download from my financial services provider a detailed account of all my investments. I extract from that crucial data to track a subset of the investments.
- I used to do that extraction by means of "normal" formulas: I'd have both the fresh data download AND the downstream, analytical spreadsheet open, and just enter "=" and then go point to the cell in the source sheet. I could then copy down to multiple rows. Repeat for however many columns I wanted.
- Now, with the new Dynamic Array functions, I use =FILTER, point to the top cell in the column(s) I want from the source sheet, enter the selection criteria, and let it rip. It "Spills" all the requested data, in rows, into the secondary sheet.
From that I can develop the analysis, summaries, etc. I don't use a Pivot Table in mine, but there's no reason why I couldn't.
So if I were in your place, that's at least another possible solution I'd want to try. Go to that front end of things and see if you can connect the Pivot Table (reporting) spreadsheet with the unsensitive data elements in the "master" sheet.....
It's only been in the last month that I learned of and began employing the Dynamic Array functions--they're new to the general Excel community, having been introduced to the "insider community" only last year. Here's a link to a YouTube video I found very helpful in grasping the concepts: https://www.youtube.com/watch?v=9I9DtFOVPIg
