file:/// appearing in formula

[SeanWorrall]

Hi Having as issue where I have a formula which contains a link to another workbook has file:/// appear at the start and I can't make it go away!
What I'm getting is:- ='file:///\\systems.private\ifs\StagingN\rest_of_the_path_etc_etc\filename.xlsx
What I want is:- ='\\systems.private\ifs\StagingN\rest_of_the_path_etc_etc\filename.xlsx
This seems to make updating links fail
Anyone had the same issue? I've tried find/replace, re-linking etc.

 

[johnny51981]

Is there a reason it needs to be a formula as opposed to just the file path (without the equals)?

 

[SeanWorrall]

Yeah sorry, it is a formula. e.g. tab sheet1, cell A1 so
='file:///\\systems.private\ifs\StagingN\<rest of the path etc>\[filename.xlsx]sheet1'!$A1

 

[johnny51981]

I'm going to guess that that is a requirement for the version/region of Excel that you are using.

I wonder if you can split the path, file name, tab, and cell addresses in different cells as Text, and then create a cell that concatenates into the full path in the way that you would like to see it without the preceding file:///? Plus, if you ever need to change one piece of that full path, it's ready for the editing.

 

[SeanWorrall]

Not seen this ever in 25 years of using excel - when you delete it, it just reappears next time.