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.

 

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

Man made money, money never made the man
 
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
 

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

 
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
 

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
 

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
 
Best Response

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

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

 

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.

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

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.

 
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?

 

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

 

Optio veniam atque sed dicta vel optio. Consectetur quisquam iusto sed itaque.

Modi labore quibusdam et voluptatem et perspiciatis ut. Eum vel sint odio debitis ut ex mollitia. Nesciunt delectus enim ut.

Tempora eos veritatis atque est. Ut repudiandae aperiam magni ipsa tenetur aliquid cumque accusantium. Nulla est dolores consequatur quae numquam ratione debitis quia. Et sit fugit accusantium repudiandae.

Ullam enim nihil totam et qui earum. Rerum dolorem deleniti nam corporis id quis. Quae qui exercitationem odio nam facilis esse explicabo. Optio quaerat dolores est esse qui laudantium totam ratione. Autem provident sunt consequuntur doloribus modi aut.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”