Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 1st 12, 08:53 AM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 2
Default 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.)

  #2   Report Post  
Old August 1st 12, 08:54 AM
Junior Member
 
First recorded activity by ExcelBanter: Aug 2012
Posts: 2
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Slow opening links between workbooks with links created in 2003 Russell Excel Discussion (Misc queries) 0 December 14th 09 02:59 PM
absolute reference to workbooks TKoel Excel Worksheet Functions 0 September 6th 07 07:46 PM
Relative vs Absolute referencing of Workbooks Ronald Dodge Charts and Charting in Excel 4 September 26th 06 10:34 PM
Relative Links becoming Absolute Links? GB Excel Programming 0 October 3rd 05 07:05 PM
Global way to set absolute links... Ruth J Links and Linking in Excel 2 June 30th 05 05:42 AM


All times are GMT +1. The time now is 10:20 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017