Relative Hyperlink Problem

[ChrisMattock]

I have a macro, that includes the following line:

Please Login or Register  to view this content.

However when the macro runs for some reason it adds a path in that I do not want, and the cell results in the following:

=IF(ISNA(IF(A6="""","""",VLOOKUP(A6,'U:\DOCUMENTS\[MasterStaffList.xlsx]Staff'!$A:$J,9,FALSE))),0,IF(A6="""","""",VLOOKUP(A6,'U:\DOCUMENTS\[MasterStaffList.xlsx]Staff'!$A:$J,9,FALSE)))[/CODE]

Anyone know why? And how to stop it? I'd like the hyperlink to be relative and not absolute.

[:) Sixthsense :)]

If the source workbook is not in open then excel will show the actual root path of that workbook in your formula's.

If you keep the source workbook in open then you won't get that root path.

[ChrisMattock]

Oh wow - that worked. Thanks! But is there a work around?

I have numerous MasterStaffList.xlsx files, each for a different month, and always want the files to refer to the MasterStaffList.xlsx ffile present in that particular folder.

[:) Sixthsense :)]

Press Alt+E+K (or Data>>Edit Links) which will get you the Edit Links Dialogue box. Click Change Source button and locate your desired file.

[ChrisMattock]

OK - but in the code where I ask that cell to be populated (a LOT of sheets), there is no way to just enter the cell as I have typed it? It seems crazy my code is being overridden unless I have the file open when I run the macro.