Pivot Table deletes on opening of workbook

[yslguru]

I'm still very much a noob so please let me know if I need to add more than I have to get feedback on this.

We have a workbook with multiple sheets that represents a mockup of custom report. i sheet has nothing but the raw data, the information that charts, graphs and pivot tables on other sheets in teh same workbook will pull from. The way we make the mockup into a working report (an Excel workbook that our accounting software can dump data into) is by inserting specific text that the accounting software we use, looks for to insert real data that comes from a SQL query provided to the accounting software. It executes the query and then looks in the xlsx file for tags it recognizes.

For example if I have a query that returns info on all clients such as name, address, email and so on and I want the Clients email (aliased in the SQL Query as sEmail1) to appear in column C on the sheet that is where all this data is dumped to, then in the row, where I want the first row of data to be copied to, I would insert something like this: main.sEmail1 . If I want the clients full name (aliased in the SQL Query as sClientFullName) to appear in column D then in the same row in column D I would insert something like this: main.sClientFullName .

The report features a pivot table. When the guy who did that part of the report was creating the charts and pivot table he needed some dummy data to work with so during the design process the sheet that will eventually contain the real data returned by the SQL Query, has a bunch of dummy data in it. Once he's done and it's time to change it so that the accounting software we use will recognize where it should place each piece of data the SQL Query returns, I remove all the dummy data and put in it's place these tags in 1 row. After making this change and hitting SAVE I check the sheet that contains the pivot table and it shows no errors or warnings.

The problem is that when the workbook is next opened the pivot table is gone. I don't know if it is removed as the workbook is closed or when it's opened only that once we open it the pivot table is gone and it was there after saving the changes made and closing it. I even tried deleeting all the dummy data except for teh firt and last row and then inserting a new row between themm and placing these tag in that row and the same thing happens; the pivot table is gone upon re-opening the workbook.

Has anyone ever heard of something like and have any ideas on what may be going on?

Thanks in advance for taking time to assist

 

[cooleomter]

I'm still very much a noob so please let me know if I need to add more than I have to get feedback on this.

We have a workbook with multiple sheets that represents a mockup of custom report. i sheet has nothing but the raw data, the information that charts, graphs and pivot tables on other sheets in teh same workbook will pull from. The way we make the mockup into a working report (an Excel workbook that our accounting software can dump data into) is by inserting specific text that the accounting software we use, looks for to insert real data that comes from a SQL query provided to the accounting software. It executes the query and then looks in the xlsx file for tags it recognizes.

For example if I have a query that returns info on all clients such as name, address, email and so on and I want the Clients email (aliased in the SQL Query as sEmail1) to appear in column C on the sheet that is where all this data is dumped to, then in the row, where I want the first row of data to be copied to, I would insert something like this: main.sEmail1 . If I want the clients full name (aliased in the SQL Query as sClientFullName) to appear in column D then in the same row in column D I would insert something like this: main.sClientFullName .

The report features a pivot table. When the guy who did that part of the report was creating the charts and pivot table he needed some dummy data to work with so during the design process the sheet that will eventually contain the real data returned by the SQL Query, has a bunch of dummy data in it. Once he's done and it's time to change it so that the accounting software we use will recognize where it should place each piece of data the SQL Query returns, I remove all the dummy data and put in it's place these tags in 1 row. After making this change and hitting SAVE I check the sheet that contains the pivot table and it shows no errors or warnings.

The problem is that when the workbook is next opened the pivot table is gone. I don't know if it is removed as the workbook is closed or when it's opened only that once we open it the pivot table is gone and it was there after saving the changes made and closing it. I even tried deleeting all the dummy data except for teh firt and last row and then inserting a new row between themm and placing these tag in that row and the same thing happens; the pivot table is gone upon re-opening the workbook.

Has anyone ever heard of something like and have any ideas on what may be going on?

Thanks in advance for taking time to assist

Hi i do not mean to offend you but are you familiar in creating pivot tables?

 

[yslguru]

Hi i do not mean to offend you but are you familiar in creating pivot tables?

The Q is not at all offensive but a very reasonable question and I do want to say thanks for asking it so politely. Too often on forums (not speaking about this one specifically but in general) some are very unkind to insulting to people asking for help.

The answer is not really but I also didn't create the pivot tables in the workbook, another co-worker with more knowledge about using pivot tables did. That said, I'm not so sure their knowledge with pivot tables is much more than mine. Since I am the report writer at the company I've been asked to assist with troubleshooting this, as the guy who did create the pivot tables is unable to explain or fix the problem.

I need to update the info I gave in my original post as I just realized in the examples I gave I left off something that may be important to know. We just started working with the software's new reporting option that uses Excel so I'm still learning about it myself. When I listed the example of main.sEmai1 I should instead had listed it as &=main.sEmail1 as that is the complete set of text we have to use to make this a report that works with the software we use.

There are 2 pivot tables, each on it's own worksheet and the data the tables look at is on a separate sheet so there are 3 sheets in total. Only 1 of the 2 pivot tables self-deletes and I'm thinking it may be that the function the one that deletes uses produces a level of error on opening the workbook that excel is forced to delete it versus showing an error however it's strange Excel provides no feedback that it has done this.

Is it OK to share an xlsx file on the forum for others to review? I don't want to do anything that breaks the rules and I could see how providing a workbook you've got issues with to others to look at and tell you what the deal is might come across as soliciting someone to do free work. I have a feeling that someone with a little more experience with Pivot tables in excel could likely spot the problem in a few seconds. The data in it is dummy data so there's no issues with security.

Thanks again for taking time to reply back.

 

[cooleomter]

I do not mean to offend you. Are you familiar in creating a Pivot table.
I have been working in pivot tables in excel. I have also encountered many problem in pivots because of my mistakes.
One is i have already created a pivot table from my database. note any changes on your database may change your pivot tables IF YOUR WORKBOOK IS SET TO REFRESH ON OPENING AND CLOSING YOUR WORKBOOK.

QUESTION: WHAT DO YOU MEAN OF DELETES YOU PIVOT TABLE?
IS IT THAT ONLY THE DATA IS REMOVE ON YOUR PIVOT TABLE AND PIVOT TABLE IS STILL THERE?
OR THE SHEET OF YOUR PIVOT TABLE IS DELETED.

AS I SAID IF YOUR SOURCE DATA (DATABASE, RECORDS, AND OTHER INFORMATION)FOR YOUR PIVOT TABLE HAS CHANGES THEN YOU HAVE TO CREATE AGAIN THE PIVOT TABLE.

Hope this will clear your questions.Godbless