Trying to reuse chart templates in a worksheet

[NatS]

I am moving in from microsoft office and thankfully things are close enough OfficeLibre that much of my old workflow is mostly seamless in the transfer – I really appreciate that.

However, one major issue that I am having trouble figuring out in LibreOffice Calc is reusing complex worksheets with multiple extremely detailed and nuanced charts preserved as a properly reusable template. In excel traditionally would create a template worksheet and it would have all sorts of function and processing and charts. I copy these template worksheets into a new file and paste in all the new raw data into the appropriate areas. Then, the functions process all the raw data into new processed cells, and finally the charts, which select areas of output from these functions graph all the processed data. A near instant processing data stream.

In LibreOffice, however, only the first part of this cascade works correctly, and not, actually the most time consuming part, the extremely carefully prepared, complex, and nuanced charts. Trying this in LibreOffice, I copy the template sheet to a calc document with the new data, I paste across all the key new raw data, the in-cell functions process all the raw data into the cells that the charts rely on … and the charts only display the old data from the original template sheet where I test ran everything. If I click on “Edit” for the chart, Calc says that the charts basically internally saved all the data from the original test run and they aren’t interested in the cells in the same page that they are supposed to reference that has all the new data I want them to graph right there next to them. It looks like in Calc I only have two options – accept these charts can only carry the original pre-baked in template-form-test data, or basically flush them out and start from scratch (I can edit them but I have to redo all the: series, label, details, etc… its basically no time-saving over starting a completely new chart from scratch.)

I can’t afford to start from scratch like this every time. If one has a big blob of processed data referenced by multiple charts, each of which takes multiple series out of multiple complex partially overlapping non-standard segments of it, graph these hundreds of points, only a few key select of which are supposed to show their data labels and key points among the hundreds have key unique formatting, error bars for some things and not other, specific pre-baked ranges, and carefully approved colors and icons for all the series, it takes me literally 5 to 10 hours to redo this work from scratch each time and then check all my work with the appropriate people. I need it to take mere seconds following pasting the new data into the template files that has all these charts just-so precisely so that I won’t need to redo them all again from scratch in unbelievable meticulous detail and then check all my work again with multiple people each time.

Is there any way to get a chart to just reference the cells in its sheet and continue to do so even after the sheet is duplicated; i.e for the chart to continue to reference the same cells in the new duplicated sheet that it is a part of so I can paste new data into those cells and have the chart update itself properly? I appreciate that someone did the extra work to have calc make sure you can’t “damage” charts by overwriting their data in new sheets when they are referencing data from old sheets. But for extremely complex sheets with extremely complex and precise charts being duplicated to be used as a template for new data being able to overwrite the old data and have the charts all adjust accordingly is a huge time-saving blessing that I can’t seem to access on Calc unless there is some secret way to achieve this. Please tell me there is a way to do this in Calc, because if there isn’t I am most regrettably going to have to go back to excel as I can’t afford to spend 5 to 10 hours on what I need to take 5 to 10 seconds.

[Villeroy]

Alternative approach with scenarios: https://ask.libreoffice.org/uploads/short-url/gJR9ckCZ106LVDN6ENPlXVg4Tz6.ods

[NatS]

I agree that file works, I just copied out one of the sheets to a file and it is still referencing data from the same new sheet in the same position as the old.

What I don’t understand is how does it do this. What is the magical preference checks, setup or whatever that allows this behavior to work correctly because I can’t get that to work in any of my OfficeLibre files doing things I know how to do or what worked for me in excel. Clearly it is possible, but it isn’t working for me.

… is it maybe a use of the .ods file? Like the behavior is correct for a .ods file, but not for a .xlxs file when using OfficeLibre? If so the problem is I have to share all these files with coworkers who give me stuff in excel and then require documents that excel can read back. Sure excel claims it can read .ods files, it certainly opens them, but it makes an amazing hash of the contents. I really need to work in .xlxs files for company comparability, I have been chastised for handing out .ods files already.

[Villeroy]

Excel can open ods. May be, it can handle the scenarios made in LO.

[NatS]

Excel can open .ods, but it does not do so well – I wouldn’t be surprised if this was intentional on Microsoft’s part. I have tried this at my work before and sent out .ods calc files. There is always something critically botched somewhere once excel opens them. At least that is so for any sheets that aren’t trivial and simple, anything meaningful in complexity has been ruined in my experience. I have been expressly forbidden to send around .ods files at my work as a result. I can work in OfficeLibre but only so long as I send out Files in Microsoft Office formats, not merely files that Microsoft Office can theoretically open, because the list of disasters on the later has been extensive here already.

[Steph1]

@NatS

I can’t afford to spend 5 to 10 hours

Then my suggestion is that you buy MS Office, more or less $100 per year. Cheaper, legal one-time licenses can be bought for less.

[NatS]

I am on linux and I don’t always have internet access to use Microsoft Teams and while people have had some success with using Docker and or Wine with Office on Linux the results are far worse than just using OfficeLibre. I would have to upend my entire everything for Microsoft Office. Its not the money, I have a subscription to Microsoft office and One Drive already and use what I can use of them.

[Steph1]

I hope that you understand that Excel and Calc will never be equal and that the compatibility between the two never perfect. Based on your initial post, I understand that you have very complex and precise spreadsheets and graphs. There is no chance that one day you will be able to open --and save-- with Calc a perfect Excel file (or vice versa).

We will be able to help you from time to time but the cost of the time spent by all of us will be far greater than the cost of MS Office with a computer running Windows. Sad, but true, in my opinion.

[NatS]

They are compatible enough for my needs. I can compose my entire files in LibreOffice in .xlxs and export them to people on Office and it has not yet once been a problem as long as I maintain .xlxs formatting no matter how much time I spend fine-tuning graphs and things.

My issue is that a data-paste over a duplicated sheet causes the charts to “retreate” to their own protected data mode instead of continuing to faithfully sample the cells I pasted over. That shouldn’t be a Libre/Office problem as it shouldn’t have anything to do with the file formatting as it is an open file behavior problem. However the open file saves, exports faithfully (or enough so for my needs) to Microsoft Office, but I can’t get what should be default behavior to work: the pasting of new data over old data to adjust a chart without the chart locking itself to isolated separate data. I can see why that would be a useful behavior if specifically set on a chart, but for it to apparently be the default behavior is quite odd to me.