Hi folks,
I thought I was familar with how to update the paths for links to other workbooks (when changing the file location of the two (or more) interconnected workbooks) - but I've experienced some bizarre behaviour. Can anyone help?...
Ordinarily, when I open a workbook with links to another workbook (whose file location I've recently changed), I get an Excel warning message asking if I want to update the data (I do), and then another warning message saying Excel the workbook has links that cannot be automatically updated (because the source workbook has changed location), and asking if I want to edit the links (i.e. change/specify the paths to the source workbooks). I do.
Here's where it gets strange. When I select 'Change Source...' and navigate to the first source file - instead of 'accepting' this and changing the source's status to 'ok', it immediately pops up another file selector window asking for the second source file. Then the third. etc. then it cycles back to the first or second source file path again. This happens relentlessly!
By a combination of canceling and/or 'updating values' and then going back to update other file paths Ive eventually got it all correctly linked again, **but I've no real idea of what I was doing or why excel seemed to be cyclically asking me for the same path details**.
As an example of one of the wierd states I got the 'Edit Links' dialogue box in, I include the attached screenshot...
It got worse than this, but even here you can see that excel seems confused about the link status of particular files (workbooks). A single workbook (e.g. Class1_WiderCurriculum.xlxs is listed twice, once where its status is 'ok', and once where it is 'Error:source not found').
Finally, I've read that such paths are normally stored 'relative' (even if displayed within cells as absolute paths), and wondered whether, in order to avoid such problems entirely in futre I could explicitly write link paths within a cell in formats something like '..\PeerDirectory\[Class1_WiderCurriculum.xlxs]!B4' , where I'm trying to specifiy that the path is 'go to the parent directory, then to a Peer Directory, and the file will be there.
It's the first (relentlessly cycling requests for updated paths) that bugs me the most, but improving my understanding of all the related problems would be amazing. Thanks in anticipation!
Bookmarks