For example…
=‘file:///C:/Users/Username/OneDrive/FolderName/FileName.ods’#$Sheet1.A1
(my line of code is massive as there are formulas using the link several times)
can I do this somehow with adding some code in somewhere?
=‘file:///FileName.ods’#$Sheet1.A1
So as long as all files are in the same folder it will not matter where they are as long as all files are in the same folder?
There is an option to save the links as relatives:
Menu/Tools/Options/Load-Save/General - Save URLs relative to file system.
Don’t worry if you see the whole path in the cells, it’s saved properly.
edit 20150916
For that you can use INDIRECT(), but it can slow down the spreadsheet used intensively.
OK, I have that box ticked already.
Just so I am clear on this, do you mean that if I rename the folder or move the folder to another location they would still find the references to the other sheet? As long as they are in the same folder maybe?
I am wondering as my docs are on online drives and if I download them on another machine with a different username will the files will be looking for the previous username.
The INDIRECT() thanks for that, that’s why it wouldn’t work when I tired to do this when I had these calcs in openoffice. I may look into this as this would give me the added benefit of being able to change the link at a later date. But am concerned about it being really slow, until I saved these as .ods they were 13MB approx(.xls) there now 1.8MB(.ods) how bizarre.
I think you can carry both files into the same folder.
I always use it in a local network, but maybe you can assign that online drives to a local driver letter, or using version 5 with LibreOffice dialogs Menu/Tools/Options/LibreOffice/General/Open-Save dialogs to connect some online services.
Instead INDIRECT() you can try with DDE().
Different size is normal .ods are zip compressed files, but there is the option to save as flat without compress.
hi,
My question is how can we use this text string as a variable ?
i.e. in a formula like: =VLOOKUP(SearchCriterion; Array; Index; SortOrder)
Array could be a referanced cell like D1… where D1 contains the text “=‘file:///FileName.ods’#$Sheet1.A1”
thus making it possible to change the “Database / Array” globally in … cell D1… without having to change each formula.
How could this could apply to “sheet name” or to “range NAMES”
i.e. change the range NAMES in … cell D1 … and all VLOOKUP formulas will point to a new sheet or range
Thanks,
Sergio
When I was using openoffice I read that if you move or add lines in which moves lines up and down you will have to re-do your vlookup formulas because they will not move to where the referred cell is now. I cant remember if this ever happened.
If you use Index and Match rather than vlookup this will be fine as they do alter and still point correctly…
Maybe someone could clarify this information or confirm as correct or false.
I tried that when I was using openoffice (using libreoffice now of course) It would not work it kept coming up as error or NA or something to that effect I cant recall.
I tried that coz every time I reloaded a document my links to other documents would all say #REF how annoying on something that’s taken a long time to code in. I now think it was because there was a space in the folder name, libreoffice seems to change the folder to the first it comes to if they are the same up until the space