[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

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
0
Justincut
Asked:
Justincut
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Phillip BurtonCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 BurtonCommented:
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
 
Phillip BurtonCommented:
Delete the ,"Open"

in other words, instead of:

=Hyperlink(myPath,"Open")

use

=Hyperlink(myPath)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now