Description: Traditionally in Libra office if one wanted to link A1 in worksheet Bob to B1 in works sheet Sam, and you had both worksheets open, you would simply place an equals sign in A1 in Bob and pick on B1 in Sam. This functionality seems to have disappeared. You can use the Hyperlink dialogue, however this is extremely cumbersome if you have a range of cells you would like to link. Actual Results: = Expected Results: cell value from external file Reproducible: Always User Profile Reset: No Additional Info: From Libra Office Help Choose File - Open, to load an existing spreadsheet document. Choose File - New, to open a new spreadsheet document. Set the cursor in the cell where you want to insert the external data and enter an equals sign to indicate that you want to begin a formula. Now switch to the document you have just loaded. Click the cell with the data that you want to insert in the new document. Switch back to the new spreadsheet. In the input line you will now see how LibreOffice Calc has added the reference to the formula for you. The reference to a cell of another document contains the name of the other document in single inverted commas, then a hash #, then the name of the sheet of the other document, followed by a point and the name of the cell. Confirm the formula by clicking the green check mark.
I can't reproduce, do you have both files saved at least once. And both are opened with the same LibreOffice, not with different versions.
Hi, Been doing some experiments to determine the cause of the issue, as yes you quite correct, it seems to work sometimes. 1. If you have a lot of links to an external file. The File becomes unstable and sometimes crashes. 2. Creating the external reference file as an XLSX file seem to be less problematic than an ODS file. 2.1 Creating the link by pointing to the cell in the reference file, creates a file://url/worksheet/sheet/"cell reference" If you now save and close the Calling file and reopen. You get several strange behaviors. 1. Cell shows #REF or #Value. Looking at the cell reveals the correct syntax, however the cell still shows an error. If you now re-point to the cell you require, the syntax and formulae do not change but the error disappears. 2.Some cells have data from the incorrect sheet in the reference file but the correct Cell reference. example If you have two sheets in the reference file. lets call them One and Two. You have an external link that says sheetONE cell D2. Data that shows in that cell is data from SheetTwo D2. 3.The link is not dynamic. If you make changes in the reference file the data does not change in the calling file. The files I have are rather large and have confidential data, else I could send them to you, can do but not on a public link, I think you can appreciate that. When I get 30 minutes will see if I can create some dummy files, that exhibit the same problems to assist you. Interestingly opening the calling file (XLSX format) in xcel takes 53 seconds In libra this takes 15 minutes and shows no errors. Opening the file (ODS format)the XLSX file saved as ODS from Libra, shows only #REF errors or data from wrong sheets in the called file data. I will try save the XLSX file from XCEL in ODS format and see if I get the same errors.
Hi, After a week of trying my best to avoid the users of the application to be forced to use Excel, I have had to throw in the towel. I have found workarounds for most of the issues in Libra and I will describe them below, hopefully to assist you in identifying the root causes. The remaining obstacle is a time issue, The files take too long to open and too long to save in Libra, to enable me to call it a working solution, furthermore although the solution works in Libra, sometimes it throws a wobbly and crashes, this very frustrating for the users. First let me describe the application to give you a strategic view then will drill down to where the issues are, and what I have managed to discover in my try and try different again, with much head banging. There are 4 reference files, with multiple sheets, that contain data that is for the most part fairly static, with changes occurring perhaps every four months. Users require access to these files in order to get reports and create customer documents, these being dynamic. These users are dispersed in many locations. All files therefore are stored on a cloud service. I at first created the referenced files as ODS and the dynamic files as OTS. In order to cut time, I at first recreated the data that was needed in the reference files in the dynamic files by linking to these files using the ///file.... etc linking. This proved problematic with ODS files as sometimes the data was returned as #REF or #Value or if using the index/match function I would get data from wrong sheets in the reference file. I then discovered by accident a work around by creating the reference files as XLSX, here the data was never returned as #REF, #VALUE or incorrect ranges. However the OTS took forever to load, 15 to 20 minutes. Thinking that perhaps OTS had an issue I recreated template files with Excel as XLTX. Libra treats the file as an OTS but cannot save it as an XLTX. However here I discovered different issues, all URL based, but some complicated by different OS's By this time I had scrapped the idea of loading the reference data as local data in the template files and all calls were made to the closed Reference files when needed. When opening the template file in 6.22 Libra on Windows 7 and 8 it would report that the file did not exist at the URL echoed. The echoed URL however was correct. Had me scratching my head for a while. I then discovered the work around was to change the default working directory path under options to the Template directory. I also changed the path for the template directory but this did neither improve or reduce problems. Now saving the XLTX files as OTS files I could get the users to use either format as an entry document and links to PDF's etc could navigate correctly without having to first save the Untitled file. 6.22 finds its way to the root more effectively than Excel with users drive letters not being a problem once you change the working directory path to the template directory. Excel itself however has a problem with the drive letter unless you first save the untitled file into the Template directory, which is counter productive. The template directory should be kept free of clutter. Not a Libra problem, but here the solution is that the links are all specified without a drive letter and the first thing the operator has to do is Type in the root drive letter for their cloud service when opening the Template file. So yes I have managed to get the solution to work in both Libra and Excel, However the Libra solution is not viable. The time to open the XLTX or OTS in Libra is close onto twenty minutes, about the same time for save. The XLTX in Excel opens the first time in about 14 seconds and subsequent openings in about 3 seconds. Saving is about 8 seconds. I hope my rambling assists. There are probably things I have forgotten to mention as I am under severe time pressure to finish this project. Please feel free to ask for further clarification. Have a beautiful day Bob
To be certain the reported issue is not related to corruption in the user profile, could you please reset your Libreoffice profile ( https://wiki.documentfoundation.org/UserProfile ) and re-test?
Hi I have reset my profile, to factory defaults, and the final error of slow loading has not improved, if anything seems to have got worse. I have not checked the ODS file functionality,as I would need to now recreate these file. *(see Note Below) Ie having the source files as ODS and OTS, instead of XLSX and XLTX. Libra sits for ever after "enable external content". As this template and the relevant source files sit on a cloud service, I had asked several users to test the app to indicate failures or any issues. The data returned was that MS 7 & 8 had issues with referencing the source files. The Echo to Screen, would indicate that LIBRA could not find '"Drive", "Path", "File name" ' The URL echoed was pointing in the right location and had the right file name, but it still complained the file did not exist, the echo and the actual URL handed to the kernel, must be different. On these users I had them change their working directory default to the templates directory which is part of the directory tree, ie it shares the same root. (Probably just pointing to the root of the tree would have been sufficient,(this just a guess), I can get these users to try that. On win 10 this problem does not seem to exist, you can leave the default working directory untouched and Libra discovers the root. The workaround I discovered is therefore. 1. Use source files as XLSX. This solved the issue of ODS files returning #REF or #VALUE after being closed and the template fired up and asking the source files for data. 2. The only issue now is a time issue. Had to advise users to use Excel until we can discover what is causing this very slow loading of the template file. EXCel 3 to 8 seconds, Libra 20 or more minutes. An aside, I did discover a strange behavior with COUNTIF. Using IF(COUNTIF(Range, condition,T,F), fails with EXCEL if the RANGE is external to the template file, and the external file is closed. Returns #VALUE and only way is to open source file. The only way to get this to work in EXCEL is to use a negative check, IF(ISERROR(Match,Criterion,Range),T,F) Here EXCEL returns the correct result, even if the source file for the range is external and the file is closed. Checked this with LIBRA this morning ad she does not have a problem using Countif to check external files, of IF(ISERROR(MATCH) Feather in your cap. * XCEL 2016 does not like files it created(XLSX), saved by LIBRA, as it reports the file needs repairing. (I do believe this has to do with some very nice features in LIBRA, that are not available in XCEL. Sort of validation data, dynamic formatting in formulae etc) Just before starting this mail I had asked LIBRA to open the template file, XLTX, on a development computer it must now be 30 minutes and still she shows updating external links and the progress bar sits at 33% Have a beautiful day Bob
Some useful or perhaps not so useful test results. Did Some tests, while I was busy with some other tasks. Using the same computer, with a 32 bit version of Excel 2016. Libra 64 bit (6.2.2) takes just over a minute to open the template, then asks you if you want to enable external links, she then takes a further 30 minutes to open the file and make it available. Once open the data updating as the user enters data and the template needs to fetch data from the source files is slightly slower than excel, but not significant for the user to notice. Saving the template as a user defined final file takes a further 30 minutes. Excel takes 15 seconds to open the file before she asks you to update links, she then take approx 40 seconds to update the links and make the template available. This is using XLSX and XLXT files as the test files. The previous results were run on a user computer using 64 bit Excel 2016. Here the average opening time was 3 seconds and updating links a further 10 seconds. Saving the file as a user defined final file takes 8 seconds. Enjoy. and hope this helps. Bob
Hi, Tonight monitored Memory use by both Xcel and Libra, after opening the template (Xltx) and enabling content updating from source files. Xcel uses 624 184 Kb and file is open and active. Libra races through until she stops at 12 253 916 Kb and progress bar now stops, that's about 45% of the memory of the un-allocated memory on the PC. (32 Gig Installed) Clues for you in in the dark. Have a beautiful day Bob
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to Bob from comment #2) > The files I have are rather large and have confidential data, else I could > send them to you, can do but not on a public link, I think you can > appreciate that. When I get 30 minutes will see if I can create some dummy > files, that exhibit the same problems to assist you. You could try https://wiki.documentfoundation.org/QA/Bugzilla/Sanitizing_Files_Before_Submission#Sanitize_file_text
odd, i think i used that feature three days ago, recheck of #129464 formula() referencing to other documents, worked normal, click on other file-sheet and 'link' is ok, today testing 7.0.0.0.a1+: no reaction on click in other sheet, and referencing cell stays as '=', as well in 7.0.0.0.a1+ as in 6.2.8.2 (which worked better in the past) ... ??? I love! loose contacts and toggeling behaviour ...
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
referenced sheet must be saved with a name, tried links to 'Untitled', no chance, no hint ...
Linked file must saved at least once.(In reply to b. from comment #12) > referenced sheet must be saved with a name, tried links to 'Untitled', no > chance, no hint ... Linked files must have been saved at least once.
Dear Bob, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping
Dear Bob, Please read this message in its entirety before proceeding. Your bug report is being closed as INSUFFICIENTDATA due to inactivity and a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information (please ignore any that you've already provided): a) Provide details of your system including your operating system and the latest version of LibreOffice that you have confirmed the bug to be present b) Provide easy to reproduce steps – the simpler the better c) Provide any test case(s) which will help us confirm the problem d) Provide screenshots of the problem if you think it might help e) Read all comments and provide any requested information Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue. Please do not: a) respond via email b) update the version field in the bug or any of the other details on the top section of our bug tracker Warm Regards, QA Team MassPing-NeedInfo-FollowUp