Problem: hyperlink with VLOOKUP

[yincision]

I have two workbooks (for here workbooks A and B)
Workbook A has two tabs (Training summary and Validation numbers)
Workbook B has several tabs but they are copies of each other
In workbook B I am using

=VLOOKUP($E45,'[Lackland Training summary.xlsm]Validation numbers'!$B:$B:'[Lackland Training summary.xlsm]Validation numbers'!$A:$A,2,FALSE)

this looks at the number in E45 on workbook B if the number matches a number in workbook A, validation numbers, column a then is inputs the corresponding data from validation numbers, column b into the cell where I have the VLOOKUP. This all works the way I want it to.

I run into the problem when I want to hyperlink.

In workbook A Training summary tab I want to create a hyperlink that opens workbook B. When I open the hyperlink my VLOOKUP changes from the previous to

=VLOOKUP($E45,'\\52MPLS-FS-108\SSI Training Records\LACKLAND\[Lackland Training summary.xlsm]Validation numbers'!$B:$B:'\\52MPLS-FS-108\SSI Training Records\LACKLAND\[Lackland Training summary.xlsm]Validation numbers'!$A:$A,2,FALSE) and gives me the #REF! error.

When I take the time to manually remove \\52MPLS-FS-108\SSI Training Records\LACKLAND\ from every instance (162 total) it works fine but I am making this for other people and explaining how to unprotect replace all and then protect again is too much for them.

I am sure this is a simple fix but I am stumped. Any help would be greatly appreciated.

Anthony

 

[Andrew Poulsom]

Welcome to MrExcel.

Your formula will show the full path to the source workbook if the source workbook is closed. If it is open it will show only the workbook name. Are you sure that your hyperlink is pointing to the correct workbook?