3/21/12

We run complex models, like really comlpex. It's not unusual for us to run a model that references back to 20+ other Excel files.

Anyway, I put together a pretty simple spreadsheet where a single cell will reference one other file, very simple. So I closed out of my work so my boss could review. He told me the model did not update, so I checked it out. The cell references were all correct, but for whatever reason they would not update. I tried a couple of things - "Formulas -> Calculation Options -> Automatic" was checked, clicking "Calculate Now" and "Calculate Sheet" would not update the model. The only way the to update the cell is to click on the formula and hit Enter or Tab. I tested the issue out on a couple of new speedsheets (both saved on the same shared drive) and it's still happening.

Not sure if its just my computer yet, currently testing the issue with different computers.

All our files are saved on a shared server (I suspect this may be a server problem, not an Excel or computer problem) and we use Excel 2007. Any thoughts?

15% Off wall st. prep Financial Modeling Training Click Here

Comments (46)

3/21/12

try CTRL+ALT+SHIFT+F9

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.

3/21/12
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

3/21/12

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

3/21/12

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

3/21/12

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

3/21/12
<span class=keyword_link><a href=//freeminimalist.ru/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?

Man made money, money never made the man

3/21/12
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

3/21/12

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

3/21/12

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.

3/21/12

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

3/21/12

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

3/21/12
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

3/21/12

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

3/22/12
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.

3/22/12
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

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.

3/21/12

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

3/21/12

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.

3/21/12

My IT guy is convinced its Excel.

Man made money, money never made the man

3/21/12

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

3/21/12
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

3/21/12

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.

3/21/12

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

3/21/12

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

Man made money, money never made the man

3/21/12

Call the IT guy...

absolutearbitrageur.blogspot.com

3/21/12

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

3/21/12

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

3/21/12

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

3/22/12

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

3/22/12
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

3/22/12

Tools > Options > Calculation > is automatic checked off?

3/22/12
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

3/22/12

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

3/23/12
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.

3/23/12

Insane... This thread made me shudder!

3/23/12

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.

9/20/12

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.

9/20/12
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

9/20/12

I just saw it. FINALLY haha. Thanks op!

9/20/12
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

11/26/12

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.

7/22/13

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.

7/22/13

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?

3/23/15

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.

5/6/15

1-Click to Unlock All Comments - 100% FREE

or
Why do I need to be signed in?
WSO is a knowledge-sharing community that depends on everyone being able to pitch in when they know something.
+ Bonus: 6 Free Financial Modeling Lessons with 1-Click Signup ($199 value)
6/8/15
12/5/16