Will some expert please answer this question?

[John Fussell]

In the Excel 10.1.6 Preferences / Calculations window, the Update
Remote References box is checked but the item is grey and I cannot
change it. The Excel help files indicate I should be able to uncheck
it. A similar question was posted last April but there was no answer.
And other questions have been posted about the Update Remote
References item so apparently there are problems with it. I would
certainly appreciate some answers or suggestions.

Thanks

 

[Jim Gordon MVP]

Hi John,

If I remember correctly, the remote references feature was creating some
problems. There were several different dialog boxes opening when Excel
opened documents with external references and they were causing some
crashes. Now I could be full of baloney, but I think the cure Microsoft
came up with was to always force the update links dialog to display when
the workbook opens rather than offer a choice in the preferences.

On the other hand, if you really want to change the preference setting
this little vba snippet will do the trick:
Sub ChangePref()
With ActiveWorkbook
.UpdateRemoteReferences = False
End With
End Sub

You might find that in some specific instances that the remote
references might cause bad behavior when opening specific files.
Probably most of the time it would be OK, though.

-Jim

 

[John Fussell]

Thanks, Jim. The problem I am really trying to solve which I thought
might be affected by the "Update External References" preference is
that I have a number of worksheets linked. In general, even though the
worksheets are not open, they are updated and they in turn, provide
source data for the "master' worksheet which is collecting all the
final data. A couple of weeks ago, two of the source worksheets quit
providing data unless they are open. I do not know what happened to
change this and I have tried re-establishing the links, totally
deleting the links and then re-establishing them, deleting all Excel
preference documents and re-booting, re-installing Excel, etc. I will
greatly appreciate any suggestions.

Thanks

John

 

[Jim Gordon MVP]

Hi John,

I don't know of anything in particular that would be responsible for
that behavior.

Did you try repairing permissions or any other troubleshooting steps
listed here?
Start with the steps on this page:
http://www.mcgimpsey.com/macoffice/office/troubleshooting.html

-Jim

--
Jim Gordon
Mac MVP
MVP FAQ
<http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs>

 

[Bob Greenblatt]

Hi John,

I don't know of anything in particular that would be responsible for
that behavior.

Did you try repairing permissions or any other troubleshooting steps
listed here?
Start with the steps on this page:
http://www.mcgimpsey.com/macoffice/office/troubleshooting.html

-Jim

Also, how about making sure calculations is set to automatic. Remember that
Excel will change its calculation state to that of the first sheet that is
opened. So, if you had saved the sheet with calculations as manual and then
later have opened it as the first sheet, the calculations mode will change.

 

[John Fussell]

Hey guys, thanks for all the help and suggestions. I tried them all
and didn't solve my problem and I am beginning to wonder if I am
trying to do something that Excel simply does not do and that my
memory is simply false that I had linked spreadsheets that were doing
it. Anyway, I figured out a workaround that seems to be working.
Thanks again.

John Fussell