Dynamic Hyperlink

[telecomadmin12]

Hi people,

I have an excel sheet with cells that contain numbers. I want to hyperlink these cells to files on a network drive. Now I have a large number of cells and don't want to update the link manually but create a formula for dynamic hyperlinks.
With each cell the location and name of the file changes slightly but predictably. Such as in cell E1 the hyperlink would look like this: \\Sfs4\telecom$\\Comcast\Invoice_06-02-2015.pdf

In cell D1 like this:
\\Sfs4\telecom$\\Comcast\Invoice_07-02-2015.pdf

So with each cell the name of the file I link to changes by one month. E.g. from 06-02-2015.pdf to 07-02-2015.pdf. How do I reflect this in the hyperlink?

The friendly name I would update manually.

Thanks.

 

[SkipVought]

Hi,

Use the INDIRECT() function to construct the path & file name usin a column to store the date, which you can increment by one month in the DATE() function.

 

[combo]

I guess you are using HYPERLINK function, you can build both arguments first (address and friendly name) and apply them in a function.

combo

 

[telecomadmin12]

Could you give me an exapmle of how this would like in the formula?

 

[SkipVought]

[tt]
="\\Sfs4\telecom$\\Comcast\Invoice_"&TEXT(E2,"mm-dd-yyyy")&".pdf"
[/tt]
Where E2 has your date. And DATE() function points to the previous date cell...
[tt]
=DATE(YEAR(D2),MONTH(D2)+1,2)
[/tt]

 

[telecomadmin12]

Thanks SkipVought and Combo.

This works.
There is one problem that came up.
My function looks like this:
=HYPERLINK("\\Sfs4\telecom$\\Comcast\Invoice_"&TEXT(E2,"mm-dd-yyyy")&".pdf","$477.56")

As the friendly name I have a dollar amount.
I have other sum functions in the sheet which should add the $ amounts in cells. Now values from cells that have a hyperlink function + friendly name like above don't get added. The value is $0. How can I solve that?

 

[SkipVought]

If 477.56 is in A1, then
[tt]
=TEXT(A1,"$#,###.00")
[/tt]
Then reference the cell containing the TEXT. BTW, a number is a number. Can't reference a number here; you need TEXT!