How to edit a Hyperlink path in Excel?

Hi, I have this Hyperlink path "\\dfs.uk.ml.com\London\FinanceShared\Finance\EMEA Mortgages\2015\01-January 2015\PVG\Reporting\1 - PV Month End Reporting Pack.xlsx" in Excel . The Folder "01-January 2015" changes every month so how can I edit it so it automatically changes Month on an Excel Spreadsheet ? I can do it with a Formula eg. Format(Now,"DD MM YYYY") but not a hyperlink
JustincutAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
Delete the ,"Open"

in other words, instead of:

=Hyperlink(myPath,"Open")

use

=Hyperlink(myPath)
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can use a formula hyperlink instead, and you can generate the path using a formula.
 
=Hyperlink(PathStart & Format(Now,"DD MM YYYY") & PathEnd,"Open")
0
 
Rory ArchibaldCommented:
NFP:

You'll want the TEXT function rather than Format, which is VBA, though.

TEXT(TODAY(),"dd mm yyyy")
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Rob HensonFinance AnalystCommented:
Looks like you will also need to formulate the Year folder part as well:

TEXT(TODAY,"yyyy")

Also rather than using TODAY() it might be worth having a fixed date somewhere in your sheet and refer to that instead.

Otherwise, as soon as the new month occurs the calculations will change and you may not have saved to a new file by that time.

Thanks
Rob H
0
 
JustincutAuthor Commented:
Hi Rob, if I use a Fixed date in Cell A1 of "01-Jan-15" and my path is this: \\dfs.uk.ml.com\London\FinanceShared\Finance\EMEA Mortgages\2015\01-January 2015\PVG\Reporting\1 - PV Month End Reporting Pack.xlsx


What would be the full formula for the Hyperlink?
0
 
Rory ArchibaldCommented:
As Phillip indicated earlier, it would be:

=HYPERLINK("\\dfs.uk.ml.com\London\FinanceShared\Finance\EMEA Mortgages\"&TEXT(A1,"yyyy\\mm-mmmm-yy")&"\PVG\Reporting\1 - PV Month End Reporting Pack.xlsx","text to display")

I've assumed that the 01 in 01 January is a month number, not a day.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If it's a date, then you yyyy\\dd-mmmm-yy
0
 
JustincutAuthor Commented:
It works. Cheers! However, is there a way I can see the full path so colleagues can see it changing from Jan to Feb? Just like when you insert a Hyperlink within Excel but with a visible variable.
0
All Courses

From novice to tech pro — start learning today.