I have created a spreadsheet to help me calculate jobs which we do. It stopped working for me after I have copied it to a different computer. The feature that doesn’t work are dynamic named ranges. Everything else such as static ranges, links to external files, coded functions which I have created is fine. Anyone has any suggestions as to where to look for solution?
Both computers are brand new, running windows 10 and newest version of Libreoffice. I just got it two days ago.
What file type are you using? (.ods, .xls …?)
Please upload your small sample files here.
My first idea was an option in LibreOffice, but you already got this answer some months ago:
You have only copied the file, or did you also migrate your user-profile?
Might you (@marus_b), please, exemplify what’s a “dynamic named range” in your case, or point me to a definition of the concept you mean. .
If I search for “LibreOffice Calc dynamic named range” on the web (with duckduckgo in my case) the first finding is Dynamic named range.
Named Ranges and Expressions doesn’t contain/mention the term “dynamic”.
@Lupp Maybe my naming convention is still from Excel, if so, please forgive me. Here is an example of what I’d consider a basic formula going into an address field of a dynamic named range:
=offset(a1,1,0,counta(a:a),-1)
@Zizi64 It’s a true ODS file. Problem is that it’s more than just one file. It’s a bunch of them, and the main one feeds data off of the other ones. What I find odd is why would it work perfectly on one computer, but not the second one?
@Wanderer the user-profile is what I need to look at. In all honesty I did not think about it before. Nevertheless, could the information from the original user profile be pulled along with the actual spreadsheet? For sure I did not intentionally do it.
How does this mechanism work in practice?
(Also with respect to already posted comments)
Concerning the “dynamic range”: OK. That’s what I guessed you meant, and expressions returning references (with OFFSET(), INDEX(), INDIRECT()) work in range definitions in LibreOffice Calc. But: Is this kind of named ranges what you get problems with, or is the issue related to named ranges referencing ranges (named ranges) from different files? Generally please try to be more precise concerning a specific issue “stopped working” and “other things work fine” rarely give sufficient information. Fortunately you are talking to the only(?) user who had a similar problem…
tdf#61743 was the very first bug I reported, and lacking experience I did it in an awkward way. However: Your files may be in unchanged positions thinking of releative paths, and yuor setting may be “store paths relative…” . Due to the mentioned bug the wrongly used absolute paths will mostly be different on the other computer nonetheless.
May this be related to your issue? It is still unfixed.
If you suspect this to be the background, try to re-create one of the failing ranges on the new computer.
The user profiles can’t be connected to documents for many reasons. However, you can call soffice.exe with a path to a different user profile. See option -env in Starting LibreOffice Software With Parameters.
Let me try to put more perspective on things, and what I have done so far (In between my original post - which now I also see being very vague) and this point in time:
Here is the scenario:
I have two computers and a WD storage device. One computer and the storage is physically located at my office, the second is at home (where I am right now). If necessary, I am remotely logging into my computer at work via Remote Desktop Connection.
Both computers have latest version of Libreoffice installed and are running Windows 10.
I have developed a spreadsheet which has several sheets on it:
It retrieves information from other spreadsheet files:
I also have coded some few basic functions. They vary from moving positions of entered data up and down to saving/opening raw information for a particular job in separate spreadsheet files. Thus, if I want to open up a quotation for a particular job, then all the info which I had to manually entered gets copied from that individual file, into that main quotation spreadsheet, and then all the calculations are taking place and a report is generated which I email to the client. (No I am not using the internal email within LibreOffice yet, I still need to learn how to use it). Everything works flawlessly as well, it doesn’t matter where and how I execute it, except for the fact that instead of numbers I get error messages (#N/A) in cells.
The main quotation file has about 50 named ranges. Some of them are dynamic, some static. I had created all this on an old computer which I had recently gotten rid of, and up until recently everything was working fine, even though copies of those files were running on both computers (the old one and the one at work). Originally I had it set up in such way that the main quotation file and the supporting configuration and material spreadsheets were located on both computers, and the raw data files which held individual quotations were located on the WD disk. However, it seemed a bit slow to me (when saving or opening), and I begun utilizing FreeFileSync utility. So what I do now is I synchronize in the following manner:
Home to WD
Work to WD
But I not only synchronize the raw data, but the quotation file + supporting spreadsheets as well. I do this because I constantly add and modify things in the configuration (such as adding new items to my validity lists, and adding new material names/quotations).
I am under the impression that this is where the problem begun. What happens now is the following:
The quotation file works perfectly when I run it from the office computer, but only the copy which is stored locally on the office computer disk. If I execute the same file from the WD drive (from office or from home), or if I execute it at home, there is an issue with those dynamic named ranges. They are simply not recognized.
Even though the behavior already begins when running the file WD file on the office computer, I reinstalled the LibreOffice at home, completely removing the user profile before the new install begun. That obviously did not help. I tried to make new functions within the same main quotation file utilizing the named ranges but with no luck. It seems as if the name and the definition, within the file itself, got disassociated somehow. However, even though I deleted the home and WD copies of that quotation file, and simply copied them from the work computer, the behavior is still the same.
So copy of the Libreoffice at work will work properly if the file is located in its local drive, but not on the external storage facility.
And it will NOT run properly at all at home. Doesn’t matter if I run it locally or if I execute it from the WD drive.
Let me know if any of it makes any sense to you please.
Intro:
Your comment now is very descriptive. (However, I don’t get absolutely clear about “quotation file”, “quotation sheet” and probably additional terms made from everyday words. I’m not a native speaker of any English, and our topic is technical in a field where accurate terminology may be indispensable.
Important:
Did my answer, and in specific the hint concerning the bug, make any sense to you? I couldn’t find a word about this in your statements.
I wouldn’t expect to be able to help you along any more without a few answers to my questions-in-return, and I’m afraid there might be explanation beyond my understanding of English.
Thoughtful:
Do you feel sure that spreadsheets are the appropriate means for what you want to achieve? Years ago (about the time when I reported the mentioned bug) I often tried to help users with (faintly) similar things. Some years later I started doubing if not I only had “helped” them to waste even more time trying to to get a working solution using the wrong tools. Again pointing to “the bug”: I got aware of it in exactly such a situation. Having found a few more questions by the same user in different forums years later makes me believe, he didn’t achieve happiness with his attempts - except the kind of happiness coming with challenging difficulties.
Are you suggesting I use a database instead?
I personally never made a database “for production” and due to experiences with databases I had to use mandatorily during my professional life I don’t like DB. Nonetheless they are an efficient amd well standardized class of tools for very many everyday processes…
As always “it depends”, If you want (need) to work “freestyle” (probably creative) with your data, if you are the sole “master of data” in your range, and if the amount of data is not really large you may well prefer sheets - and omit all the built-in means of saftety, security, integrity, rights-management a DB comes with, and the means for presentation (reporting, printing, exporting), too. .
As soon as you need very complicated formulas filled into thousands of cells, and split data (multiple documents) where different people need to work with different parts, you will have problems for the years to come.
Oh I now need some macros…
Damn, for what reason got this so slow…
Yesterday it worked lika charm, but…
Our partner BrunchingCrowd & Cie changed the format of the data they deliver. How to cope with…
How can I move this all to a second computer…
I completely understand where you’re coming from. And to be honest with you, given the fact that we are a small outfit, and not much potential for huge expansion, I think i’ll just stick to sheets. I never dealt with databases either, and I love the flexibility and potential that spreadsheets offer.
I’m trying to understand the syntax of the -env options. Hopefully your suggestion will actually get me somewhere. 
I wouldn’t read the hint to the -env optiona as a suggestion concerning your problem. You just asked for something like connecting a different user profile to a document, and I told you the only way I know to do so. I feel sure you won’t solve your issue this way.
But. You should really answer my qusetions. Repeating them (differently worded):
OFFSET(), INDEX(), INDIRECT() but without refering to a named range in a different document?To answer your questions, all those ranges which utilized the formulas you mentioned only refer to ranges within the same spreadsheet (different sheets, yes, but never out of the scope of the same file)
I created a new dynamic range with the same definition (in my case it was: OFFSET($MatSel.$H$2,1,0,COUNTA($MatSel.$H:$H)-2,1)) but no positive result - it still shows (#N/A) error.
I changed the definition to static: $MatSel.$H$3:$H$10 and still the same error.
I tried the “recalculate” and “hard recalculate” functions but no luck.
The function that the range is being subjected to is just a simple Match function.
I still can’t find unambiguous answers to my questions starting with the second one. Please regard the wording exactly.
I didn’t suggest (e.g.) to re-define a completely internal named range, and I don’t know if the cells you refer to may contain a reference in turn to whatever.
Regarding your posts there must be references to different files. These are the suspects as I see it. You should re-establish the references under the “new computer’s file system”. Try it having open all the used files, and by selecting the foreign cells and ranges with the mouse.
Use the Detective to be sure concerning dependencies of cells showing an unexpected error.
We should try to finish this part now. Take the needed time. No answer expected on sunday.