LO Document has become unusable .....?

[Villeroy]

@Clearwell_T

Anyone got any ideas?

LIke the vast majority of today’s spreadsheet users, you try to implement a database on spreadsheets.

[Clearwell_T]

Hi ms777,

Thanks once again for the help.

@ms777

I have removed some 50,000 empty columns on the right side

Aahh, yes, that’s one thing that intrigued me ie: why the default document has so many columns (see my answer to Villeroy below). I had meant to get around to removing them. They weren’t my doing.

@ms777

There was an orphan object on the left side. You can see that in the Navigator pane. I removed that, too

Actually, that was a place holder for my lame attempts at a horizontal bar chart, to display trade times or some such. Thank you very little for being so callous about my carefully constructed artistic piffle!?!

@ms777

You use a mixture of normal functions and array functions (like ={…}, e.g. in col V). I am not sure if array functions are necessary. I recommend to use them only if you are exactly knowing what you are doing.

The only formulae that are using array enter, are precisely the ones that need it. I’ve been very meticulous about testing whether array enter is necessary. It seems to me that it’s very obvious when it is and when it isn’t required.

@ms777

In your Named Ranges there is a mixture of upper row bounds, e.g. 187, 3617, 97989, 1048576. Maybe this is partly caused by me cutting the rows, but you should look over that.

That’s definitely mostly a result of your cutting. Although I was trying to aim for a sensible number on the DNR spans for each section. Small, medium and large row number requirements. I am just in the middle of trimming down the DNRs to the bare minimum, and rationalising the lengths again in light of further experience and your helpful comments. Ta for pointing this out.

@ms777

Please note that the handling of complete columns (e.g. B:B) in LO is not possible like in Excel

I’ve seen the update to this below.

@ms777

All office documents are zip files. If you copy your .ods, rename it to .zip, open, you will find the content.xml I was mentioning

Great, but I’m still unsure what a “sensible” size for this file would be??

@ms777

P.S.: There is no indication that the LET function is the culprit …

Yes, I think I was starting to come to the same conclusion. I replaced my User folder, and I seem to be making better headway now, modulo trying to handle too much raw data. See my comments below.

Thanks again ms777. Appreciated.

CT

[Clearwell_T]

Hey @Villeroy,

Thanks very much for the reply.

@Villeroy

LIke the vast majority of today’s spreadsheet users, you try to implement a database on spreadsheets.

I’m really intrigued by this comment. I’m totally sure I am one of those people, but only through shear self-admitted ignorance.

LIke the vast majority of today’s spreadsheet users …

This seems like the most telling part of your statement. Does this mean that there isn’t a suitable product that’s “fit for purpose” out there in the market, or just that, like me, inexperienced users such as myself, assume unattainable results by focusing on the wrong product solution??

try to implement a database on spreadsheets.

What I find most misleading in this scenario as I find it is, why would the default configuration of a spreadsheet include over a million rows and north of 50,000 columns, if that wasn’t intended to lead neophytes such as myself into believing that there was some serious raw capabilty under the hood just itching to be unleashed.

My failing no doubt.

Can you recommend a suitable equivalent database version of what LO does, that I should look into?

Thanks again for your help. Much appreciated.

Regards.

CT

[koyotak]

@Clearwell_T

saving interim copies as I went along throughout the day.

Did you save the intermediate copies as new files: “safe as…”? Then find the most recent file that works well. If you only have one intermediate file that you’ve always overwritten, then you’ve made a typical user error. If you’re extremely lucky, you might find the cell with the programming error. Or you can go through the program code line by line, counting the parentheses, and spot a typo or missing part of a line. There are programs in C++ that let you test your code for errors. I doubt LO programmers have developed anything similar. Therefore, I recommend you will copy the contents of the table and paste it into the new blanc table as plain (unformatted) text to at least save the sensitive data. A very nice job for rainy holidays

[Clearwell_T]

Hey Koyotak,

@koyotak

Did you save the intermediate copies as new files: “safe as…”? Then find the most recent file that works well. If you only have one intermediate file that you’ve always overwritten, then you’ve made a typical user error.

Yes, although I’m a total LibreOffice newb, I’m long in the tooth with file management experience. I have a long sequential list of Save As…'s. The problem so far has been actually working my way back through them to find which will open, whilst simultaneously troubleshooting from other angles with the help of others here.

It very much looks like now that I’ve managed to get the latest “Save” open, I assume they all will open, so not much to be gathered there, unless the issue DID creep in at a particualr point in the day.

As discussed above, I’m going on the hunt for eradicating LET functions as my next port of call.

Thanks again for joining in. Much appreciated.

CT

[koyotak]

Hallo @Clearwell_T
I used to be a maintenance technician, but unfortunately, that’s no longer valued these days: “If only you could have found the error faster, earlier, and more easily!”
I circumvented a non-obvious error by splitting the suspect entity into three parts and testing each one individually. If the error occurs in all three parts, I move out and on to the system. Before I poke around in the system, I question as many people as possible who have handled the system, including events that deliberately don’t fit together with something else. Putting two things together that don’t fit together at all is my special talent, one that only a few people possess.

[Clearwell_T]

Hey Koyotak,

I’m a recovering recording studio engineer, and it takes a certain breed of person to excel at that job (not saying I did) too.

That perfect blend of technical competence, musical understanding and a psychology PHD is needed to avoid many “customer exempt (of course )” session derailing problems.

Thanks again for helping keep me in line.

CT

[koyotak]

High @Clearwell_T
I welcome any engineer/manager who stays in the background, is willing to experiment, and wants to see the broadest possible picture, unless we cooperate well. Well, I’ve had very few of that kind, and I’ve learned nothing.
Except that that good symbiosis was a catapult that shot into progress, 10 years into the future, as an architect once explained to me. Since then, I’ve been trying again and again to shoot myself down and bring myself down to my current, also stagnant, position.
I’ve had many of the other kind, the ones with arrogance and terrible patronizing behavior, even those with narcissistic tendencies. The collaboration was disastrous, but I learned a great deal from them. I mean that explicitly in terms of character and human qualities, the effects of fractures and injuries. A significant advantage in old age

---

I hope and wish that you were at least able to save the valuable data, text, information, and values. The layout and formatting with the recessive calculations, however, are much easier to regenerate and can be better structured the second time around.
Have a good time!