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.

Comments (47)

Mar 21, 2012

try CTRL+ALT+SHIFT+F9

    • 1
Mar 21, 2012
CaliforniaAnalyst:

try CTRL+ALT+SHIFT+F9

Tried, does not work. I believe Ctrl+Alt+Shift only works with Excel 2003.

Mar 21, 2012

^^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.

Mar 21, 2012

you format them into text? sometimes if the formula is too damn complex the cell wont update

Mar 21, 2012

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.

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Mar 21, 2012
<span class=keyword_link><a href=//www.wallstreetoasis.com/finance-dictionary/what-is... rel=nofollow>HF</a></span>:

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?

Mar 21, 2012
RE Capital Markets:

"='S:\Shared Folder\Shared Sub-folder[Referenced File.xls]Referenced Sheet'!$A$1".

^^That is the absolute path, is it not?

Mar 21, 2012

since you reference to a sheet on the server, make sure it's the absolute path

Mar 21, 2012

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.

    • 1
Mar 21, 2012

Alt+E+K works, but I shouldnt have to refresh the links everytime.

No #REF! errors, cell references are good.

Mar 21, 2012

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

Mar 21, 2012
rufiolove:

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.

Mar 21, 2012

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.

Mar 22, 2012
RE Capital Markets:

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.

Mar 22, 2012
rufiolove:
RE Capital Markets:

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.

Mar 21, 2012

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?

Mar 21, 2012

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.

Mar 21, 2012

My IT guy is convinced its Excel.

Mar 21, 2012

"Hello this is IT. Have you turned it off and on again?"

    • 1
Mar 21, 2012
Nabooru:

"Hello this is IT. Have you turned it off and on again?"

I actually tried rebooting, didnt help.

Mar 21, 2012

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.

Mar 21, 2012

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.

Mar 21, 2012

I tried changing the reference to and from absololute references, still doesn't work.

Mar 21, 2012

Call the IT guy...

Mar 21, 2012

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.

Mar 21, 2012

Also, in the pop-up window after I hit Alt-E-K, "Item Update" is checked to "Automatic", so its not that either.

Best Response
Mar 21, 2012

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.

    • 2
Mar 22, 2012

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

Mar 22, 2012
Nabooru:

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.

Mar 22, 2012

Tools > Options > Calculation > is automatic checked off?

Mar 22, 2012
pingafrita:

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

Mar 22, 2012

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.

Mar 23, 2012
RE Capital Markets:

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.

Mar 23, 2012

Insane... This thread made me shudder!

Mar 23, 2012

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.

Sep 20, 2012

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.

Sep 20, 2012
looking4anything:

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.

Sep 20, 2012

I just saw it. FINALLY haha. Thanks op!

Sep 20, 2012
looking4anything:

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.

Nov 26, 2012

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.

Jul 22, 2013

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.

Jul 22, 2013
milank:

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?

Mar 23, 2015

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.

May 6, 2015

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

Jun 8, 2015
Comment
May 4, 2018
Comment
Dec 5, 2016
Comment