Forum Discussion
Excel Pivot Table corruption after loading saved file
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