Workbook Name Automatic Update

[cmeier7]

Hi, I am trying to keep a hyperlinked function up to date when I change the filename of a workbook(such as through SaveAs.) For instance, if I change the filename to Book2.xlsm, the hyperlink formula (below) will be rendered useless.

<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">=HYPERLINK("[Book1.xlsm]'Sheet1!A1","To Reachback") </TD></TR></TBODY></TABLE>
I then have CELL("filename") in cell A3

This gives me

<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Q:\RIC\Support\Reachback\[Book1.xlsm]Sheet1 </TD></TR></TBODY></TABLE>

I want only the text "Book1.xlsm" in cell A2, so that it will always be updated to the current workbook name. I will then use cell A2 in the hyperlink formula as follows

<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">=HYPERLINK("[" & A2 & "]'Sheet1!A1","To Reachback") </TD></TR></TBODY></TABLE>

My question is this- how do I get cell A2 to read only the book name "Book1.xlsm" ? I have the below function in cell A2 but it returns a #VALUE, which is not what I want (I want Book1.xlsm)!

=MID(A3,FIND("[",A3)+1,FIND("]",A3)-FIND("[",A3)-1)

Any Help? Thanks!

 

[RolfJ]

Gee, this formula works on my end! Could it be that it should refer to A3 rather than A1? After all you said the =CELL("filename") formula was in cell A3!

Hope this helped,
Rolf

 

[cmeier7]

Ok, it worked this time for me too (I have no idea why it wasn't working before.) It is close! but I have one additional issue that's going on. My Workbook name (in place of the Book1.xlsm of my previous post) is "BCS Milestones 7.30.09.xlsm" and the MID function is only returning "7.30.09.xlsm". It seems as if the MID function is grabbing the last string without spaces, and not from the start of [ to the end ]

 

[ravishankar]

Hi
this should give you the file name within brackets

=MID(A3,FIND("[",A3),FIND("]",A3)-FIND("[",A3)+1)

Ravi