Get full path of external cell reference, even if target file is open?

[TheWaterbug]

Suppose I have ListOfLinks.xlsx that has formulaically-generated hyperlinks to directories and subdirs in a big directory tree like:

1. BigDirectory\
   1. ACMERocketCars\
      1. Products\
      2. Customers\
   2. MarvinMartianArmaments\
      1. Products\
      2. Customers\
         1. Mars\
         2. Earth\
         3. Jupiter\

etc. Each of those dirs and subdirs may have contents, but I want the hyperlinks to open just the directories.

The wrinkle is that ListOfLink.xlsx lives outside of BigDirectory, and BigDirectory may get moved or renamed by a customer. So I need a way for the user to point to BigDirectory and restore the validity of the links. I was able to do this by putting Root.xlsx at the root of BigDirectory, e.g. BigDirectory\Root.xlsx, and then ListOfLinks.xlsx has one cell that links to Root.xlsx, e.g. D5 contains =[Root.xlsx]Sheet1!$A$1.

If Root.xlsx exists, but is NOT open, I can use FORMULATEXT(D5) to get the full path to wherever BigDirectory lives today, e.g. /share/user/stuff/BigDirectory/ and then append my text list of dirs and subdirs, and all the links work.

If BigDirectory gets moved or renamed, Excel properly prompts the user to find Root.xlsx, and upon Edit Source: Change Source: Close, everything works again.

But only while Root.xlsx is NOT open. As soon as I open Root.xls, the path disappears from FORMULATEXT (and from the actual formula bar) and then there's no way to extract the path. Note that I can't use a relative paths "down" from where Root.xlsx is, because ListOfLinks.xlsx may be anywhere, so I need to know where Root.xlsx exists in the file structure.

Is there a way to do this without VBA?

 

[Anthony47]

Maybe:
In a cella of ListOfLink.xlsx insert the formula

=CELL("filename",'D:\Directory\SubDirecory\[Root.xlsx]Sheet1'!$A$2)

Now, if Root.xlsx is open the formula will return the full path for the file; if Root.xlsx is closed the formula probably will return #N/A but FORMULATEXT will now show the full path.

So it would be easy to get the information either from the value or from the formulatext

 

[TheWaterbug]

Maybe:
In a cella of ListOfLink.xlsx insert the formula

=CELL("filename",'D:\Directory\SubDirecory\[Root.xlsx]Sheet1'!$A$2)

Now, if Root.xlsx is open the formula will return the full path for the file; if Root.xlsx is closed the formula probably will return #N/A but FORMULATEXT will now show the full path.

So it would be easy to get the information either from the value or from the formulatext

Yes, I thought of this same solution last night!

But it only works if Root.xlsx is open. If I open Root.xlsx, the formula calculates properly, and I can extract the path. If I close Root.xlsx, everything is still Ok.

But as soon as I do anything to trigger a recalculation, the CELL formula becomes #N/A.

 

[TheWaterbug]

Well, since each state makes and breaks the opposite method, I suppose I can combine the two with some error check. But it's extraordinarily ugly:

https://www.kan.org/download/BigDirectory.zip

I doubt I'd ever deploy this because it's ugly and would be very non-obvious to troubleshoot. But I can use it for my own purposes.

 

[TheWaterbug]

"error checking"

 

[Anthony47]

I seem you didn't get through to the end
As I said, with the process I suggested:

if Root.xlsx is open the formula will return the full path for the file; if Root.xlsx is closed the formula probably will return #N/A but FORMULATEXT will now show the full path.

With reference to the XL2BB minisheet and to the image:
-using the "remote file" MULTI_C30307.xlsm in D:\DDownloads
-In K1 the formula =CELL("filename",'D:\DDownloads\[MULTI_C30307.xlsm]Foglio5'!$AF$1)
-In K3 I calculated the directory that host the remote file using the formula

=TRIM(IF(ISERROR(K1),LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(K1),"=CELL(""filename"",",""),"[",REPT(" ",100)),"'",""),90),LEFT(SUBSTITUTE(K1,"[",REPT(" ",100)),90)))

Beware: copy the formulas from above in my message, not from XL2BB minisheet

In simpler language, the formula in K3 says:
IF(K1 is in Error, then Get the Path using Formulatext(K1), else Get the path from K1 value)

The following XL2BB minisheet shows the result when the remote file is closed; the attached image shows the result when the remote file is open

byWnG_C30316_Elenco capitoli numerato (version 1).xlsb
	J	K	L	M	N	O	P	Q
1	CELL:	#N/D			=CELL("nomefile",'D:\DDownloads\[MULTI_C30307.xlsm]Foglio5'!$AF$1)
2
3	PATH:	D:\DDownloads\
4
5
6
Foglio1

Cell Formulas
Range	Formula
K1	K1	=CELL("nomefile",'D:\DDownloads\[MULTI_C30307.xlsm]Foglio5'!$AF$1)
K3	K3	=TRIM(IF(ISERROR(K1),LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(K1),"=CELLA(""nomefile"";",""),"[",REPT(" ",100)),"'",""),90),LEFT(SUBSTITUTE(K1,"[",REPT(" ",100)),90)))

 

[TheWaterbug]

In simpler language, the formula in K3 says:
IF(K1 is in Error, then Get the Path using Formulatext(K1), else Get the path from K1 value)

Ah, I couldn't figure out how to get it all done in one cell. Well done! Thanks!