Solved

Posted on 2011-05-06

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

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

13 Comments

=Sheet2!F22+Sheet3!C22

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

If you are still flummoxed do a search for [ - external formulae always have square brackets in them.

Hope you get this annoying issue sorted.

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.

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.

==========================

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.

==========================

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.

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.

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.

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!!!

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

Title | # Comments | Views | Activity |
---|---|---|---|

make top menus bigger font | 3 | 25 | |

Copy Value of cell in formula | 1 | 25 | |

Vba and formula to change date | 4 | 17 | |

Ensuring all processes are complete before continuing | 6 | 16 |

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

Connect with top rated Experts

**16** Experts available now in Live!