Displaying Linked Filenames

[Andy Pilkington]

I have a master file that is linked to two other spreadsheets I wondered if there was a formula to allow me to display the names of the linked files in a given cell. Please can anyone help? Thank you in anticipation.

 

[Peter_SSs]

I wondered if there was a formula to allow me to display the names of the linked files in a given cell

Perhaps you could give us 2 or 3 examples of exactly what might be in that 'given cell' and the corresponding results that you want returned from them?

Consider XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[Andy Pilkington]

Hi Peter, the master [comparison] file is "020 000 Comparison.xlsm" and the two files I have linked to it are "Demonstration Comparison File.xlsm" and "Empty Starting File.xlsm". The linked files will change as I compare the results from different projects and I would like to have the files that I am comparing clearly displayed in the spreadsheet whilst I am looking at the results of the comparison, rather than having to go to "Data" / "Edit Links". Does this make sense? Perhaps I am lazy but if it can be dome it would be most helpful.

 

[Peter_SSs]

I'm assuming that data is being brought in to the master file via formulas in the master file. Is that correct?
If so, I'm asking for examples of the formula(s).
If not, what is the nature of the link between the master file and the other files? How are they linked?

 

[Andy Pilkington]

A typical link ...
='C:\AP 2006\Clients\[Client Name]\File Comparison\[Demonstration Comparison File.xlsm]MC Summary'!I10
If there is a formula that can truncate the cell contents to display only "Demonstration Comparison File.xlsm" then that is what would work for me.

 

[Peter_SSs]

Suppose that formula is in A2, try

=TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(FORMULATEXT(A2),"[",REPT(" ",100)),100)),"]",REPT(" ",100)),100))

BTW, does the formula actually have the other square brackets where you have [Client Name]?

 

[Andy Pilkington]

No the square brackets simply indicate that I have removed, a specific client's details, no square brackets. Thanks for this I'll play around and let you know how I get on.
Thanks for your input.
Andy

 

[Peter_SSs]

No the square brackets simply indicate that I have removed, a specific client's details, no square brackets.

In that case you can try this shorter formula

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A2),"]","["),"[",REPT(" ",100)),100,100))

For either formula if you have any very long file paths/names then you may want/need to increase all the '100' values in the formulas

 

[Andy Pilkington]

You're a genius, I still can't follow the logic of the formula but it works. Thanks again Peter.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

Briefly, the logic is this.

Start with
='C:\AP 2006\Clients\Client Name\File Comparison\[Demonstration Comparison File.xlsm]MC Summary'!I10

Replace the ] with [
='C:\AP 2006\Clients\Client Name\File Comparison\[Demonstration Comparison File.xlsm[MC Summary'!I10

Replace both [ with 100 spaces (I've used dots here to represent spaces & less of them but hopefully you get the idea.
='C:\AP 2006\Clients\Client Name\File Comparison\.................................Demonstration Comparison File.xlsm.................................................MC Summary'!I10

Now start 100 characters in from the left and grab 100 characters. That will grab the filename but have a lot of space characters before it and after it
.....................Demonstration Comparison File.xlsm....................

TRIM removes the excess space characters leaving you with the file name.
Demonstration Comparison File.xlsm