Excel 2010 - using text in a cell to refer to a link path

I have the typical problem of excel swapping paths in our links.  It is getting really frustrating.

I am wondering if there is a way to get the value of a cell into the formula for the link.

Please see the attached image.

TIA
excel-links.jpg
gpchicago08Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gpchicago08Connect With a Mentor Author Commented:
I don't want the users to have to do it.  It can be hard-coded into the button.  It would be used just to fix the links.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Use the Indirect function, e.g.

=INDIRECT(a11)

This will return the same as

=X:\INTRANET.FILES\01_Human Resources (etc)!C5
0
 
gpchicago08Author Commented:
INDIRECT needs the file link to be open.  This won't work.
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
gpchicago08Author Commented:
What about getting a constant from Name Manager into the link formula?

LinkPath=X:\INTRANET.FILES\01_Human Resources\[GP-ProjectList.xlsm]Active Projects

How can I get this constant into the cell

='X:\INTRANET.FILES\01_Human Resources\[GP-ProjectList.xlsm]Active Projects'!C5
=LinkPath!C5  <---???
0
 
Rory ArchibaldCommented:
You can't. You'll have to use code, INDIRECT with open workbooks, or use an add-in like Morefunc.xll

Can you not just use the Edit Links dialog?
0
 
gpchicago08Author Commented:
Can code be written to fix the links?  I can then add it to a button and the world will be right again!
0
 
Rory ArchibaldCommented:
How would it know what to fix them to? It's probably as easy to use the Edit Links dialog, or a simple Find/Replace.
0
 
gpchicago08Author Commented:
I obviously can't do this simply.  I'm going to start a new question for the button programming.
0
All Courses

From novice to tech pro — start learning today.