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)!
Any Help? Thanks!
<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!
Last edited: