Problem: It used to be easy to set up a link between one workbook and another workbook. When you sent the file to someone else, they had to answer the Update Links message. Starting with Excel 2007, you now get a security warning that the recipient can easily ignore. This means they may not get the current values.
Strategy: Links were used as an important technique by many people. 99.9% of the people used links for good things. It is possible for someone to do bad things with a link. Hence, Microsoft stopped updating links. You will have to do some retraining of the people who receive your workbooks.
When the workbook that contains links is opened, the Security Warning bar above the worksheet says, "Automatic update of links has been disabled.". Click Enable Content to have the links update.
Again, 99.9% of the time, a link is trying to do something innocuous like getting last year's budget total from a closed workbook.
Strategy: I spent a good hour trying to figure out how to have Excel simply ask the question, "Do you want to update the links or not?" There is no way to get this question. I talked to the folks on the Excel team and, indeed, if you tell Excel to ask the question, the question is going to come in the form described above.
Yes, if you tell Excel to ask, it "asks" by telling you that the links are disabled. You have to convince Joe, the VP of Marketing, to actually click the Options button and then click Enable. Joe can easily ignore the question and just start working with out-of-date data. Frankly, this is more dangerous than whatever Excel was trying to protect you from.
It is easy for me, because I don't have a VP of Marketing that I have to deal with. I feel bad for you, though, because there are a lot of Excel rookies in those higher ranks, and they really want an excuse to not have to fill out their budgets. This new link system just fans the flames.
Here are your options (aside from staying with Excel 2003). If you choose File, Options, Trust Center, Trust Center Settings, External Content, you have the three choices shown here.
- Link choices starting in Excel 2007.
If I wrote that screen, I would have made the middle option be: "Completely freak out Joe in Marketing by telling him that there is a security risk in his workbook and offer to help protect him by not giving him the current numbers from the server."
If you choose option 1, Excel will update the links without asking. If you choose option 2, you get the security warning that the recipient can ignore. If you choose option 3, the links won't update.
Before you send the workbook to Joe, you might as well visit the Trust Center and make the decision for Joe. If Joe has network access to the linked files, choose the unrecommended option 1. If Joe doesn't have access, choose option 3.