OK I'm now replying to my own posts...
Actually one of the formulas was different. The ones that use FIND work ok, the ones that use SEARCH are problematic with two or more sheets. This is due to the ones that use FIND referring to a cell in that worksheet. For an explanation of this see here;
http://www.xldynamic.com/source/xld.xlFAQ0002.html
So I now have two formulas:
=MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1)) - 5),
FIND("[", CELL("filename", A1)) + 1, 255)
or one from here
http://www.excelbanter.com/showthread.php?t=153010
=MID(CELL("FILENAME",A1),FIND("[",CELL("FILENAME",A1))+1,FIND("]",CELL("FILENAME",A1))-FIND("[",CELL("FILENAME",A1))-5)
For me they both work, but which one is more elegant?
Many thanks
Benjamin
Quote:
Originally Posted by Benjamin Newton
Hello,
I realise that this is by no means a current thread but having just tried it out I thought I should feed back my findings:
If you have two worksheets open that both use any of these formulas the first one opened will display correctly until the second one is opened, whereupon it will display the filename of the second file. Pressing F9 to recalculate will result in both worksheets displaying the filename of the currently active sheet.
The formula is great, but is there anyway around the above little problem?
Kind regards
Benjamin
|