Calc - issue with loading .xlsx pivot table

Running V 6.2.7.1 on Win 10

I am trying to load this file, from the Ministry of Justice, which contains a pivot table:

Dropbox - File Deleted

I have screenshots from a colleague to show me that the file opens in, I assume, Excel, but it won’t open in Calc.

I get the error message shown in the image.

I would be grateful for any ideas, please.

Polly

image description!

The sheets are stupidly formatted such that for all remaining unused columns (similar for rows on some sheets but that doesn’t matter here) a custom style is applied for custom width. Excel uses 16384 columns, Calc only supports 1024 columns so far, hence the warning is displayed. In this case you can ignore it safely. The file is opened, it’s just a warning displayed before the view.

Edit: 2019-09-13T16:04+02:00

Seeing the newly added screenshot now it seems there’s nothing missing, but just that Excel presents things slightly different. If in Calc you click on the list button in B23 for the Offence page/filter you get the same items presented as in the Excel view for which in B23 it says (Multiple Items). You can have the same multiple selection in Calc just within the filter list, and if chosen the same 19[CDFGEH] items (and selected 02: Female in B17) you get the same numbers as in the screenshot (and B23 then even says - multiple -).

Thank you…I wondered it was something like that. And it is the MoJ, so yeah badly formatted.

Yes, I can open it, but part of the file is missing. And because of the missing data, I cannot interrogate the pivot table on the fourth tab. Essentially, it makes the pivot table unusable.

Interpreting your comments, am I right in assuming that any work around would involve finding someone to open it in Excel and reformat it?

I’ve edited my opening post to add a screenshot of how it should look in Excel. Unfortunately, the screenshot comes from my antagonist and it would be difficult to ask him to do my work for me. It is a tense exchange, as is so often the case on social media :confused:

Bottom line, then, is I need Excel?

Glancing at the file structure it doesn’t indicate there would be data in the excess columns. You didn’t say what data is missing. However, the document file has 5 sheets. The last sheet is huge (uncompressed 94MB with data in A1:V137405) and is marked hidden and thus not displayed. It is loaded and visible in the Navigator (F5) as HO Principal Offence 2019 and can be activated using menu Sheet → Show Sheet… I don’t see data missing, just most rows on that sheet being hidden. I don’t have Excel to compare.

Apologies, I thought I had added the image of how it appears in Excel. Done that now.

And…while have been playing with it, I have solved my problem. I wasn’t seeing all the control boxes, but I have found a way in through the control boxes that I can see.

Thank you…talking it through with you has helped me find my own solutions. :slight_smile:

Please, if the answer solves the question click :heavy_check_mark:.

Yes, I finally saw that. Thank you again.