Creating absolute links between workbooks
 

I hope someone can help! My issue seems to be that Excel stores relative links between workbooks so when they are moved away from each other the links are essentially "lost"....

I create a workbook (say A.xlsx) which contains links to another workbook (say B.xlsx) - I then move the workbook containing the links, which in this example is A.xlsx. However, the links to the other workbook assume that the predecessor workbook, B.xlsx, has moved too (even though it hasn't). These links would be pulling data from B.xlsx to A.xlsx - sometimes a grid 100x100 or more.

So various cells in Sheet 1 of A.xlsx link to various cells in Sheet 2 of B.xlsx and I move A.xlsx
The link within A.xlsx still assumes the same relative position of B.xlsx ...
so if these are originally saved in the same folders, then after moving A.xlsx (B.xlsx staying where it is) - A.xlsx contains links that still think B.xlsx is in the same folder as it, i.e. that B.xlsx moved too.

This is obviously an issue as B.xlsx doesn't always get moved alongside A.xlsx and in these cases, I can no longer tell from the destination spreadsheet the true source of the data.

My query is how to stop this, and whether it's possible to create absolute links between workbooks so that this detail isn't lost on moving the workbooks? I don't think hyperlinks are the solution because the data pulled through from B.xlsx to A.xlsx needs to be 'workable' in A.xlsx (summing, finding maximums, etc.)

One solution suggested was to use $ signs. I found that this keeps the cell reference absolute (so if I drag the formula left-right or up-down then the same cell is linked to), but this does not stop the issue of relative workbook references... so if I move a workbook with link ='C:\Documents and Settings\My Documents\[Test1.xlsx]Sheet1'!$A$3, it still assumes that Test1.xlsx has moved alongside the workbook which is linking to it! So the link may become ='C:\Documents and Settings\My Documents\Excel Work\[Test1.xlsx]Sheet1'!$A$3