Thanks Jon, I'm struggling to combine them though.
I've got...
=CELL("filename") in D2...
and...
=REPLACE(D2,1,FIND("@",SUBSTITUTE(D2,"\","@",LEN(D2)-LEN(SUBSTITUTE(D2,"\","")))),"") in D3...
and..
=SUBSTITUTE(LEFT(D3,FIND("]",D3)-1),"[","") in d4
D4 is the filename without the path and sheetname, which I'm actually trying to put into the blue coloured part of the formula below:
=HYPERLINK("[
here]"&ADDRESS(MATCH(A11,'Bank Statements'!A:A,FALSE),1,4,1,"bank statements"),"Click to see it...")
Ideally, all in the hyperlink formula.
I also tried using:
"["&MID(CELL("filename",D2),(FIND("[",CELL("filename",D2))+1),(FIND(".xls",CELL("filename",D2)))-(FIND("[",CELL("filename",D2))+1))&"]"
...which does a nice job of returning just
[filename], but then I can't work the right syntax to put that into the hyperlink formula.
(I can't believe excel doesn't have simple way to either reference a filename without a path or have hyperlinks cope with whatever the active workbook is called, if the link stays within that workbook. - This all seems rather complicated
![Confused :confused: :confused:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
)