?
Solved

Links between workbooks

Posted on 2011-05-06
13
Medium Priority
?
302 Views
Last Modified: 2012-05-11
I've noticed that from time to time I'll open a workbook and find it comes up with something like "This workbook is linked to another one, do you want to update the linked values?" However I never deliberately link workbooks, so these links have got there accidentally. The link is easily broken, but it shouldn't have been there in the first place.

Can anyone tell me how this could happen? I'd like to know so that whatever it is, I don't do it. I suspect it must have something to do with copy and paste from one workbook to another, but I often do that and suspect if this is the cause then it doesn't always create a link, and I wonder why.

Thanks
0
Comment
Question by:StuartOrd
  • 6
  • 5
  • 2
13 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35709583
This happens when you copy cell with formulas from one workbook to another.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35709595
If you have a cell with a formula like this:

=Sheet2!F22+Sheet3!C22

And you copy this cell to another workbook, Excel will create a link between the files.
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
ID: 35709831
If you want to find the lionks Press CTRL + ~, this will toggle between formulae and value mode.  Then you can scan through the sheet to find oout where the links are.
If you are still flummoxed do a search for [ - external formulae always have square brackets in them.
Hope you get this annoying issue sorted.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:StuartOrd
ID: 35711084
OK, so I've created 2 workbooks called Links test 1 and Links test 2. I put the following entries in Links test 1:

Sheet1
A1:1
A2:2
A3:3
A4:4
B4: =A1+A2+A3+A4 (it displays the answer 10)
B7: =Sheet2!A1+Sheet3!A1 (it displays the answer 300)

Sheet2
A1:100

Sheet3
A1:200

Now I copy Links test 1 cell B4 and paste it into Links test 2. I guess any cell would do, but I pasted it into the equivalent cell for now, Sheet 1 cell B4. It shows the same formula as in the first workbook, ie =A1+A2+A3+A4. It displays the answer 0 since it is looking as the cells A1 etc in the same workbook.

However if I copy Links test 1 cell B7 and paste it into Links test 2 again in the similar place (Sheet 1 cell B7) the formula becomes ='[Links test 1.xls]Sheet2'!A1+'[Links test 1.xls]Sheet3'!A1. If I alter cell A1 in Links test 1 to 1000 say, both Links test 1 Sheet1 cell A7 and Links test 2 sheet1 cell B7 both become 1200 immediately. I guess if I closed Links test 2 and re-opened it, I'd get the familiar "do you want to update" prompt.

However the puzzle to me now is, why isn't cell A4 in Links test 2 a linked formula?

As a completely odd observation, I tried Ctrl + ~ in Links test 2 as suggested by ComputerAidNZ, and cell B7 now displays "14-Apr-03"!! It has taken on a "custom" format of dd-mmm-yy. What caused that? If I click in the cell it still shows the "link" formula as above. Changing its format back to General reverts it to previous "1200". Search for [ works well, thanks.
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
ID: 35711827
OK, so I've created 2 workbooks called Links test 1 and Links test 2. I put the following entries in Links test 1:

Sheet1
A1:1
A2:2
A3:3
A4:4
B4: =A1+A2+A3+A4 (it displays the answer 10)
B7: =Sheet2!A1+Sheet3!A1 (it displays the answer 300)

Sheet2
A1:100

Sheet3
A1:200

Now I copy Links test 1 cell B4 and paste it into Links test 2. I guess any cell would do, but I pasted it into the equivalent cell for now, Sheet 1 cell B4. It shows the same formula as in the first workbook, ie =A1+A2+A3+A4. It displays the answer 0 since it is looking as the cells A1 etc in the same workbook.

However if I copy Links test 1 cell B7 and paste it into Links test 2 again in the similar place (Sheet 1 cell B7) the formula becomes ='[Links test 1.xls]Sheet2'!A1+'[Links test 1.xls]Sheet3'!A1 {This is by design so that yiu can copy formulae between sheets and files too!}. If I alter cell A1 in Links test 1 to 1000 say, both Links test 1 Sheet1 cell A7 and Links test 2 sheet1 cell B7 both become 1200 immediately. I guess if I closed Links test 2 and re-opened it, I'd get the familiar "do you want to update" prompt. {All this is by design, butif you don't want it - it can be a pain.  Huge and complicated spreadsheets are full of these kinds of links, purposefully.  If you don't want the link  transferred, then copy the formula from the formula bar at the top- of the worksheet and paste this, it'll only paste the =Sheet2!A1+Sheet3!A1 part and not the link}

However the puzzle to me now is, why isn't cell A4 in Links test 2 a linked formula? {This is not because it is not a link, just copied.  Also by design else everything you copied from one workseet to another would be a link and would not be useful, whereas links copied between files are very useful.  If you want to have the link, then, type in cell B4 (I assume you meant B4) =[Link1.xls]Sheet1!$B$4 then it'll be linked - if that is what you required. }

As a completely odd observation, I tried Ctrl + ~ in Links test 2 as suggested by ComputerAidNZ, and cell B7 now displays "14-Apr-03"!! {You have somehow applied a date formula to the cell - if you use a CLEAN workbook, the cells have no formatting applied.}It has taken on a "custom" format of dd-mmm-yy. What caused that? If I click in the cell it still shows the "link" formula as above. Changing its format back to General reverts it to previous "1200". Search for [ works well, thanks.
0
 
LVL 2

Assisted Solution

by:ComputerAidNZ
ComputerAidNZ earned 2000 total points
ID: 35711839
I am trying to make the comments stand out since the last post was truly unclear!!  A bit of colour would have done the trick, but no colour on this forum - unless someone can enlighten me!!

=============================================================
OK, so I've created 2 workbooks called Links test 1 and Links test 2. I put the following entries in Links test 1:

Sheet1
A1:1
A2:2
A3:3
A4:4
B4: =A1+A2+A3+A4 (it displays the answer 10)
B7: =Sheet2!A1+Sheet3!A1 (it displays the answer 300)

Sheet2
A1:100

Sheet3
A1:200

Now I copy Links test 1 cell B4 and paste it into Links test 2. I guess any cell would do, but I pasted it into the equivalent cell for now, Sheet 1 cell B4. It shows the same formula as in the first workbook, ie =A1+A2+A3+A4. It displays the answer 0 since it is looking as the cells A1 etc in the same workbook.

However if I copy Links test 1 cell B7 and paste it into Links test 2 again in the similar place (Sheet 1 cell B7) the formula becomes ='[Links test 1.xls]Sheet2'!A1+'[Links test 1.xls]Sheet3'!A1
=============================================================
 {This is by design so that you can copy formulae between sheets and files too!}.
=============================================================
If I alter cell A1 in Links test 1 to 1000 say, both Links test 1 Sheet1 cell A7 and Links test 2 sheet1 cell B7 both become 1200 immediately. I guess if I closed Links test 2 and re-opened it, I'd get the familiar "do you want to update" prompt.
=============================================================
{All this is by design, butif you don't want it - it can be a pain.  Huge and complicated spreadsheets are full of these kinds of links, purposefully.  If you don't want the link  transferred, then copy the formula from the formula bar at the top - of the worksheet and paste this, it'll only paste the =Sheet2!A1+Sheet3!A1 part and not the link}
=============================================================

However the puzzle to me now is, why isn't cell A4 in Links test 2 a linked formula?
=============================================================
{This is not because it is not a link, just copied.  Also by design else everything you copied from one workseet to another would be a link and would not be useful, whereas links copied between files are very useful.  If you want to have the link, then, type in cell B4 (I assume you meant B4) =[Link1.xls]Sheet1!$B$4 then it'll be linked - if that is what you required.}
=============================================================

As a completely odd observation, I tried Ctrl + ~ in Links test 2 as suggested by ComputerAidNZ, and cell B7 now displays "14-Apr-03"!!
=============================================================
{You have somehow applied a date formula to the cell - if you use a CLEAN workbook, the cells have no formatting applied.}  
=============================================================
It has taken on a "custom" format of dd-mmm-yy. What caused that? If I click in the cell it still shows the "link" formula as above. Changing its format back to General reverts it to previous "1200". Search for [ works well, thanks.
=============================================================
0
 

Author Comment

by:StuartOrd
ID: 35711937
Thanks for your explanations. Kocking off the easy one first, the workbook was clean as far as I know, but not to worry, it was a minor issue.

Yes, I realise that A4 in Links test 2 wasn't a link - I was just surpised as the software is differentiating between a formula with data sources on the same sheet, which it didn't treat as a link, and a formula that took data from other sheets in the same workbook, which it did treat as a link. This is not intuitive in my opinion! And also not obvious to other colleagues I've spoken to who don't understand this.

We are engineers and use Excel frequently for engineering calculations. When a calculation needs to be repeated for a new case, say, then we might copy the file to a new name and re-use it, which gives no problems, or we might copy relevent parts only to a new or related workbook. If we inadvertantly make links, then someone altering data in the first workbook will modify results in the second one and if not understood this could lead to erroneous results when the second one is used again.Granted the warning about links should alert the person, but the potential for error is there. When calculations relate to safety in particular, we can't afford such mistakes.

Is there any way of switching off the generation of links?

Thanks for showing me how to paste the formula to prevent it becoming a link. I need to remember that.
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
ID: 35712964
What version of Excel are you running?
0
 

Author Comment

by:StuartOrd
ID: 35712996
2003 SP3
0
 
LVL 2

Expert Comment

by:ComputerAidNZ
ID: 35713255
I don't think there is a way to do it in 2003 for all workbooks being created (hope anyone can correct this if they know a foolproof way to prevent ANY paste action being set as a link), but I can suggest that you change the way you copy and paste (especially copying).  If you get into the habit of copying by the following method, you will have control over what and how the clipboard items are pasted.
Try to copy from the formula bar only, this way you'll only copy the actual formula and not the link itself

Extra ramble:
The reason for suggesting this is, that as a collective, in your business you'll have different people at different levels and if everyone is trained to perform function in the same manner, it'll save makling mistakes that can (and I can understand errors in engineering can cause the space shuttle to miss the moon) prevent costly errors.  Unfortunately, many people do not like change and they are reluctant to change the way they have been self taught.  It is because of the myriad of different ways that a function can be performed, that professional training is essential.  If users are self taught they can be incorrectly self taught, after all, imagine the chaos if everyone taught themselves how to drive! (I think that is why NZ drivers are so crap!! ha-ha)  Good trainers look at the business and tailor lessons to suit the way users will be using a package, the idea is to prevent bad habits from damaging worksheets.  Often workbook protection is taught to assist in preventing corruption.
If you are able to suggest to your peers some ground rules this will go a long way towards eliminating potential disasters.
0
 

Author Comment

by:StuartOrd
ID: 35713458
That's helpful and perceptive! I started computing when 640k was a huge memory and we used 5 1/4" floppy disks! Programs were simpler then and we self taught everything. Programs often have several ways to do exactly the same thing, and we select the most convenient or quickest. However this links issue is a nasty trip wire in my opinion - Microsoft please make link-creation a paste special option only!!

I've been trying your suggestion, but it doesn't seem to work for me. If I click cell B7 in Links test 1, I see the formula that I'd like to get into the other workbook in the formula bar ("=Sheet2!A1+Sheet3!A1"). I highlight the contents of the formula bar and Ctrl+C to copy, I then click anywhere on the sheet in Links test 2 and the formula bar shows a link which changes as I click different cells. Links test 2 is not active as its blue border is still light whereas Link test 1 has a bright border. I press Enter and there's nothin been pasted into Links test 2, but the cell in Links test 1 I was copying from now says "='[Links test 2.xls]Sheet1'!$C$10". Now I'm really confused.....

It's late here (GMT+1) so I'll leave trying more to the morning.
0
 
LVL 2

Accepted Solution

by:
ComputerAidNZ earned 2000 total points
ID: 35713565
Although I had difficulty following your note above, I think I see youre dilemma.
Once you have copied the formula from the formula bar, press ESC, since the worksheet gets put into edit mode, this will not empty the clipbboard, only exit edit mode.  THEN you can go to the other worksheet and paste.  Without pressing ESC all sorts of weird things go on!!!
0
 

Author Comment

by:StuartOrd
ID: 35714426
Yes! I had difficulty writing it too, as I couldn't believe my eyes at times.
I've been an Excel user for more years than I can remember, and whilst I recognise the possibility of some poor self teaching, I'd still not have guessed that pressing Esc to resolve this.
Weird indeed.
Many thanks, great help, now I can teach my colleagues! (amd maybe apply for some CPD in IT skills!)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question