Hi All,

I have hyperlinks in a sheet and I have used the Hyperlink Function
(=HYPERLINK("[forecast-template-final.xls]b248","Enter COST")).

The problem I am facing is users change the file name. Is there any
way to make this hyperlink dynamic so that the appropriate file name
is captured.

Any suggestions.??

I thought of having a cell where I can pull the file name and have the
hyperlink refer to this cell. Will this work? What is the most
effective way of addressing this problem.

Thanks for your time
Vishnu

A multi-step approach. Say we want to "jump to" a workbook currently named
poster.xls. The name of this workbook may change in the future. First we
put a marker in poster. In A1 and A2 enter:

=CELL("filename",A1)
=SUBSTITUTE(LEFT(A1,FIND("]",A1)-1),"[","")

Now back in the main workbook in A1 and A2 we put:

=[poster.xls]Sheet3!$A$2
=HYPERLINK("file:///" & A1)
to see:

C:\Documents and Settings\Owner\Desktop\poster.xls
file:///C:\Documents and Settings\Owner\Desktop\poster.xls

Because the formula in A1 is a simple link rather than a hyperlink, it can
be refreshed in the usual way if poster.xls is either changed or moved.

--
Gary''s Student - gsnu200783 gsnuxx


" wrote:

Hi All,

I have hyperlinks in a sheet and I have used the Hyperlink Function
(=HYPERLINK("[forecast-template-final.xls]b248","Enter COST")).

The problem I am facing is users change the file name. Is there any
way to make this hyperlink dynamic so that the appropriate file name
is captured.

Any suggestions.??

I thought of having a cell where I can pull the file name and have the
hyperlink refer to this cell. Will this work? What is the most
effective way of addressing this problem.

Thanks for your time
Vishnu