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 - 2:35pm

CaliforniaAnalyst:
try CTRL+ALT+SHIFT+F9

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

Man made money, money never made the man
 
Mar 21, 2012 - 2:30pm

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.

absolutearbitrageur.blogspot.com
 
Mar 21, 2012 - 2:40pm

<span><a href=//www.wallstreetoasis.com/finance-dictionary/what-is-a-hedge-fund-HF><abbr title=hedge fund>HF</abbr></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?

Man made money, money never made the man
 
Mar 21, 2012 - 2:51pm

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

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

Man made money, money never made the man
 
Mar 21, 2012 - 5:40pm

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

No #REF! errors, cell references are good.

Man made money, money never made the man
 
Mar 21, 2012 - 3:16pm

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.

Man made money, money never made the man
 
Mar 21, 2012 - 5:24pm

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.

Man made money, money never made the man
 
Mar 22, 2012 - 11:06am

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 - 1:09pm

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.

Man made money, money never made the man
 
Mar 21, 2012 - 5:53pm

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?

Man made money, money never made the man
 
Mar 21, 2012 - 6:33pm

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

I actually tried rebooting, didnt help.

Man made money, money never made the man
 
Mar 21, 2012 - 7:17pm

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.

Man made money, money never made the man
 
Mar 21, 2012 - 7:34pm

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

Man made money, money never made the man
 
Mar 21, 2012 - 8:23pm

Call the IT guy...

absolutearbitrageur.blogspot.com
 
Mar 21, 2012 - 8:31pm

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.

Man made money, money never made the man
 
Mar 21, 2012 - 8:43pm

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

Man made money, money never made the man
 
Best Response
Mar 21, 2012 - 8:57pm

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.

Man made money, money never made the man
  • 2
 
Mar 22, 2012 - 12:58pm

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.

Man made money, money never made the man
 
Mar 22, 2012 - 4:52pm

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

Man made money, money never made the man
 
Mar 22, 2012 - 4:55pm

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.

Man made money, money never made the man
 
Mar 23, 2012 - 4:40pm

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 - 8:58pm

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 - 10:07am

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 - 11:49am

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.

Man made money, money never made the man
 
Sep 20, 2012 - 1:43pm

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.

Man made money, money never made the man
 
Nov 26, 2012 - 1:24pm

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 - 11:45am

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 - 11:54am

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 - 8:45pm

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 - 3:30pm

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

Start Discussion

Popular Content See all

This Fucking Sucks
+48OFFby Prospective Monkey in Investment Banking - Mergers and Acquisitions">Prospect in IB-M&A
Why would any associate+ banker choose a BB over EB?
+29IBby Intern in Investment Banking - Mergers and Acquisitions">Intern in IB-M&A
PE isn’t the best way to get into b-school
+26BSCHby 2nd Year Associate in Private Equity - LBOs">Associate 2 in PE - LBOs
Share a day that looked like a Suits/Billions episode
+20IBby Intern in Investment Banking - Generalist">Intern in IB - Gen
What's Wrong with Warburg Industrial & Business Services?
+16PEby 1st Year Analyst in Investment Banking - Mergers and Acquisitions">Analyst 1 in IB-M&A
Slow Weeks?
+15IBby 1st Year Analyst in Investment Banking - Mergers and Acquisitions">Analyst 1 in IB-M&A

Total Avg Compensation

February 2021 Investment Banking

  • Director/MD (9) $911
  • Vice President (31) $349
  • Associates (158) $231
  • 2nd Year Analyst (97) $151
  • Intern/Summer Associate (92) $144
  • 3rd+ Year Analyst (23) $145
  • 1st Year Analyst (366) $131
  • Intern/Summer Analyst (304) $82

Leaderboard See all

1
LonLonMilk's picture
LonLonMilk
98.5
2
Jamoldo's picture
Jamoldo
98.4
3
Secyh62's picture
Secyh62
98.3
4
CompBanker's picture
CompBanker
97.9
5
redever's picture
redever
97.8
6
frgna's picture
frgna
97.6
7
Addinator's picture
Addinator
97.6
8
NuckFuts's picture
NuckFuts
97.5
9
bolo up's picture
bolo up
97.5
10
Edifice's picture
Edifice
97.5