Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

This is not related to programming but has been puzzling me for a couple of days. I have an Excel workbook with multiple sheets that I use as a tool that calculates the values I've given in input against data from green screens. After comparing them, I will save the input sheet and output sheet in a separate Excel document and continue with other data inputs. But whenever I run the tool, all the Excel sheets that I've saved previously are being modified with the latest results.

I found the problem, which is: "I am creating a new document by clicking Ctrl+N from the tool which is creating a link to the tool".

Now my query is, Why is this link being created and able to modify the results of all documents?

share|improve this question
    
why negative votes for this? –  Naresh Dec 24 '12 at 11:18
2  
If your formulae look like =+[book1.xls]Sheet1!A1+[book.xls]Sheet1!A2 i.e. they contain references to the original workbook, then changing the original data will probably cause all linked workbooks to be updated when opened. Removing the workbook references should help. You can also check for Workbook Connections on the Data tab (at least it can be found there in Excel 2010, I'm not sure about 2007). –  Karan Dec 24 '12 at 15:40
    
@Naresh Presumably because posted on a site devoted to programming (see faq). –  pnuts Dec 26 '12 at 19:39
add comment

migrated from stackoverflow.com Dec 24 '12 at 15:20

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 1 down vote accepted

The quickest/dirtiest solution might be to Copy each of your input/output pairs of sheets and Paste Special/Values, to convert all formulae therein (including references to other sheet or books) into their resultant values. This cannot be undone later, except with Undo.

More selective, would be to break the links.

However there is a more flexible option (also under Data/Connections/Edit Links) under Startup Prompt, where you could choose Don't display the alert and don't update automatic links. This should freeze the values as when created, but the chances are that at some point they may be updated, even if by mistake, anyway.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.