Formula information displaying correctly, but link

Question

In the picture above I have placed the following formula in cell J1:

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

I originally created the file on my local C: drive and personal download folder. I then created hyper link formulas in I4, and copied down, that build on the path name J1. This worked fine and dandy when I tested it.

PROBLEM

When I move the file to the network drive, the value in cell J1 showed the new new network path. However when I click on the link it would open my personal download folder where I originally created and saved the workbook.

What I have tried

I thought there might be some value saved some where that was not updated when I moved the file since I used explorer to move the file as opposed to saving the open workbook in a new location. To over come that I tried saving the file in its new location with no effect. I also tried save as with no effect either.

What I was trying to achieve

I was trying to achieve an automatic link that would allow the user to click on the drawing link and it would open the associated file independent of how they have mapped or reference the network location. It would also automatically update the path should the folder be moved or renamed.

What went wrong? is there a better way of doing this?

Answer 1

Potential Cause

When using the =HYPERLINK formula, excel also generates a hyperlink as if you added one manually by right clicking on the cell. It then also formats the text with an underline and blue/purple text. This "manually" created hyperlink sits on top of the formula so it gets selected first. It is this "manual" hyperlink that does not update.

Work Around

Select all cells with the issue. Right click to bring up the menu and then select remove hyperlink. This removes excels "Manual" hyperlink, but leaves the cells hyperlink formula which still works and is updated.