Excel Help! - Cells do not update when they reference another Excel file
Problem: Excel Formulas Not Updating Automatically
We run complex models, like really complex. It's not unusual for us to run a model that references back to 20+ other Excel files. I put together a pretty simple spreadsheet where a single cell will reference one other file, very simple. When I closed out of my work so my boss could review, he told me the model did not update. The cell references were all correct, but for whatever reason they would not update.
Solution: What to Check and Where to Get Help
The first thing to do is to run a series of preliminary checks, including:
- Verify cell references are correct
- Confirm that “Formulas -> Calculation Options -> Automatic” is checked
- Click Calculate Now
- Click Calculate Sheet
- Alt+E+K to refresh links
- F9 to recalculate formulas
- Verify that your links reference the absolute path of the linked spreadsheet
- Check if sheet updates on a coworker’s computer -> If it doesn’t, it could be a server issue and you’ll likely need to get your IT team involved
If these tips don’t help, it could be an issue with using different versions of Excel.
What to do When You Change Excel Versions?
Hopefully your IT team has come up with a plan to update spreadsheets when they upgrade versions of MS Office. If not, they should be prepared to handle a lot of help desk calls. Here are a few tips from MSFT Office on using excel with earlier versions:
- Open, edit and save file in Compatibility Mode
- Convert files using a file converter download
In OP's case, it was an issue between Excel 03 and 07. The sheet was created in Excel 03, but when it was opened in Excel 07 a "Security Warning" disabled the automatic update of links. To fix this issue he had to click on the Security Warning and enable the links to update automatically.
Excel Help Forum
If these steps don’t work, there are countless Excel help forums you can reach out to for assistance. With more than 500,000 Wall Street-minded community members, when you post to WSO you’re likely to find someone who has encountered a similar issue. There are other help forums out there, such as Reddit, Excel Forum, Mr. Excel or Chandoo Forums – but these are not specific to finance.
The Wall Street Prep Financial Modeling Course is also a great way to strengthen those Excel skills.
try CTRL+ALT+SHIFT+F9
Tried, does not work. I believe Ctrl+Alt+Shift only works with Excel 2003.
^^all you have to do is f9, no need for ctrl+alt+shift. This will not solve the problem though as this is just a recalc shortcut which op said he did through the toolbar by pressing calculate now.
you format them into text? sometimes if the formula is too damn complex the cell wont update
Check the links on the file, there may be an issue due to the files being on a server and being temp loaded to your computer.
Yes, I thought about this. Cells in formula ref back to shared drive. For example, formula in referenced cell will be "='S:\Shared Folder\Shared Sub-folder[Referenced File.xls]Referenced Sheet'!$A$1".
^^That's normal when using a shared drive, correct?
^^That is the absolute path, is it not?
since you reference to a sheet on the server, make sure it's the absolute path
Try refreshing your links (alt / e / k). If when you click update values it gives you an error it's a problem with the file path.
Alt+E+K works, but I shouldnt have to refresh the links everytime.
No #REF! errors, cell references are good.
You could use indirects instead of direct linking to the external sheets... simple enough syntax and then you don't have to ever worry about updating the links
Indirect link? Could you elaborate, please? I don't think I understand the difference between a direct and an indirect link.
Just figured out indirect references, its not going to fly.
Any other suggestions? This is kind of a big deal, I really don't want to have to call Microsoft customer support.
I am going to test this on a coworker's computer.
Why won't the indirect references work... on the one sheet you have that pulls from the other 20 sheets, you have a control sheet where you drop in all 20 workbooks and sheet references and then you write an indirect formula which references the workbook and sheet name, from there you can pull in any cell you want from those files and composite onto your new workbook... Would take a little bit of time to replace the old formulas but you do this ONE time and you are set for good. This wouldn't work if there are thousands of references but if you don't have a ton of references you could to a find and replace for the given cells in those sheets that you want to return... that would absolutely work.
I could do that, but I would rather figure out why Excel isn't working the way it's suppose to.
Anyway, it seems like we at least found a simple and temporary solution.
Just got off my coworker's computer, Excel still isnt working.
I am thinking this is a shared server issue.
Are there any other ways to update linked cells manually, other than what has already been suggested?
It might be a server issue...the place I used to work at did that kind of multi-file linking stuff and I remember the database/IT guy saying how much hell it played on the server sometimes
Edit: All I can think of is Alt+ARA (refresh connections) and also Alt+AO to view your workbook connections and go from there.
My IT guy is convinced its Excel.
"Hello this is IT. Have you turned it off and on again?"
I actually tried rebooting, didnt help.
Damn. Path looks fine too. Usually at this point I would ask the IT guy to come take a look at it...good luck on the fix.
Greaaaat, just great.
I just moved on to a different project with a different model, because I have spent way too much time thinking about this. Now Tabing and hitting enter in the formula doesnt update the cell values, neither does Alt+E+K. No #REF! error, the cell just doesnt update with the new value (just stays exactly the same), even though the reference is correct and the referenced cell is obviously updated. I constructed this model months ago and it was working fine.
This is fustrating.
I tried changing the reference to and from absololute references, still doesn't work.
Call the IT guy...
IT guy is standing over my shoulder as I type this.
So I tried this on my desktop (instead of the shared drive) by copying over my test files on to my comp, its still not working. So it may not be the server.
The only thing I can think of is that its a Excel 2003/2007 issue. These models were created in Excel 2003 and we have sinced switched over to Excel 2007. But we havent had issues until yesterday.
Still fustrated.
Also, in the pop-up window after I hit Alt-E-K, "Item Update" is checked to "Automatic", so its not that either.
OK, I figured it out. It's compatibility issue between Excel 03 and 07.
On my boss's comp (he uses Windows 7, I am on XP), a warning window will pop when you try to save a spreadsheet that links to other spreadsheets saying "Warning! - Compatibility issues blah blah blah, cells wont update blah blah blah and more stupid shit". For some reason, this window doesnt pop up on my comp. Basically, I have to have the referenced spreadsheet open and then open the spreadshet with the referenced formulas after and it should update. I need to test this out, but it's working so far.
So basically, Excel is fuckin gay.
Can't you just save the 2003 sheet as 2007? Or is that going to screw up the other 20 files that point to it
Possibly, but I would have to convert a lot of files, and that's too much of a time suck. Besides, everything was working fine just a couple of days ago.
Tools > Options > Calculation > is automatic checked off?
I think that only works with 03's old toolbar, not with 07's new ribbon style.
But "Automatic" is check in "Calculation Options" (under the "Formulas" tab).
Oh snap, just fixed it. It was so simple that it was pretty difficult to catch.
Basically, there was an inconspicious security prompt between the ribbon and function bar that said the links were disabled for "security" reasons. Should work now, will keep testing.
Oh god. Honestly this came through my mind but for some reason I didn't say it cause I thought you might find it insulting or I might sound stupid lol.
Insane... This thread made me shudder!
INDIRECT is a volatile function--it can't reference external sheets either (iirc), can only refer to internal ranges. It's a very powerful function when you start abusing it with ADDRESS to create systematically spaced ranges if you're doing some intense array multiplication formulas and you don't want to constantly redefine them.
bumping this old thread.
How was this fixed? Running into this issue now. Only way to update cells that have formulas linked to another workbook require me to hit F2 and then enter.
How'd the op fix this? Or does anyone know of another way? I've tried everything mentioned in this post, but nothing has worked. Can't figure out what the op is talking about when he spoke of the inconspicuous security prompt.
In Excel 07 - see under the ribbon. Is there a "Security Warning"? If yes, does it say "Automatic update of links has been disabled"? That was my problem.
I just saw it. FINALLY haha. Thanks op!
How shitty did you feel when you saw that? I remember I wanted to fucking headbutt my computer into oblivion when I figured it out- I wasted like two days trying to trouble shoot that isue with our IT dept. Excel is so fucking gay sometimes.
I've been having the same problem. I Enabled the Security Warning - Automatic update of links. Then prompted with window that say, "This workbook contains one or more links that cannot be updated • To change the source of links, or attempt to update values again, click Edit values. • To leave the links as is, click Continue". I click Continue. The only way I'm getting the links to update is to make sure the source workbook is opened first then the destination workbook is open. Links will only update if I have both workbooks open.
In order to update links in the current workbook (the one containing links) from a freshly saved source file (the one linked to), you need to click Edit Links->Update Source having previous selected the source file. Then pressing f9 will refresh the current workbook. Pressing f9 alone does not seem to update values from source files which are not open in the current workbook's excel session.
Do you feel better now, little man?
Hello:
I am late in the game - have tried all of the above in Excel 2013 ProPlus to update simple cell links between workbooks - none work. . . .
Given my chimp status, could someone be so kind as to walk me through the process from the beginning - i.e.,
Step 1. uncheck all boxes in the "Trust Bar" (a nonexistent entity according to Excel help, which first tells you to go there, as opposed to somewhere else, I suppose).
I have been navigating the mysteries of Excel since before Bill met Melinda, and this one has me up a tree.
Thanks.
I'm in the same place with 2010. I have put many weeks development time into a sheet and now the links die between sheet 1 and 2 and only display 0 een though theres pefectly good data in sheet1. I can copy a cell or range frm 1 to a new sheet I jsut made and links will be fine, but cant make sheet 2 wok again. I am talking 2 weeks work to move everything to a new sheet and see it all break again. I cant find any error messages though I have suspeted for a while that th nice busy body who designed this animal had disabled MY links for MY own good. If anyone gets a solution or seees the a&*X!ole responsble fo Excel on the street let me know please
Eius quo sed odit illo tempore. Fugit numquam dolorem sit. Rerum eum voluptates et eos. Asperiores vel consectetur eligendi omnis sed nobis.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Ut est error inventore cum. Sit necessitatibus nisi sequi iure iusto. Alias id quasi doloribus ipsam.
Et deserunt esse non quasi sed cumque sunt. Minus numquam et asperiores corrupti aut quia. Dicta illum qui harum minima occaecati et nemo. Odio voluptas quasi dolorem ut soluta possimus eius. Esse soluta sunt beatae veniam aut.
Perferendis ullam harum aperiam. Reprehenderit repellendus quia quo blanditiis harum.
Dignissimos blanditiis amet sunt et qui alias ad. Est mollitia aut impedit quisquam reprehenderit exercitationem quia. At harum quidem culpa recusandae.
Voluptatum molestias nesciunt officiis vel assumenda est et. Illo adipisci quia quae non accusamus unde totam. Quaerat cupiditate dolorem hic iure. Perspiciatis tenetur molestiae iste alias.
Exercitationem sed consequatur officia aliquid. Illum quasi in veritatis.
Eos dolor ut temporibus ullam quas. Perspiciatis et nostrum fugiat eaque ipsam esse. Sunt quis quae est assumenda eius veniam ipsam. Vitae quos aut ut et natus id. Omnis eum est voluptas doloribus rerum. Rerum perferendis nihil culpa quam id laudantium.
Distinctio sequi excepturi eos vero. Veritatis soluta non reiciendis repudiandae. Dolorem facere aut quia velit non ea dolorum.