Ask Your Question
0

CALC Is there a way to link formulas to other cells in files in the same folder...

asked 2015-09-11 18:29:55 +0100

King_ZZ gravatar image

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?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
1

answered 2015-09-15 22:12:36 +0100

m.a.riosv gravatar image

updated 2015-09-16 23:17:50 +0100

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.

edit flag offensive delete link more

Comments

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.

King_ZZ gravatar imageKing_ZZ ( 2015-09-18 02:57:56 +0100 )edit

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.

King_ZZ gravatar imageKing_ZZ ( 2015-09-18 03:05:33 +0100 )edit

1) I think you can carry both files into the same folder.

2) 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.

3) Instead INDIRECT() you can try with DDE().

4) Different size is normal .ods are zip compressed files, but there is the option to save as flat without compress.

m.a.riosv gravatar imagem.a.riosv ( 2015-09-30 23:56:23 +0100 )edit
0

answered 2015-09-16 12:37:07 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit flag offensive delete link more

Comments

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.

King_ZZ gravatar imageKing_ZZ ( 2015-09-18 02:43:32 +0100 )edit

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

King_ZZ gravatar imageKing_ZZ ( 2015-09-18 02:47:34 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-09-11 18:29:55 +0100

Seen: 663 times

Last updated: Sep 16 '15