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.)