"Manual" option grayed out in Edit Links window

Hello Everyone,

I have some formulas in a destination workbook that reference data in a source workbook.

QUESTION 1:

I would like to disable the automatic update of these links, and instead require the update process to be triggered manually. But the manual radio button in the Edit Links window of the destination workbook is grayed out. What to do?

QUESTION 2:

As you might infer from the first question, the update process for these links is currently set (stuck) to automatic. My understanding is that all cells in the destination workbook with external references to the source workbook should not be automatically updated or otherwise recalculated, unless and until the source workbook is opened. However, I seem to be experiencing a different behavior. For example, I created a column in the destination workbook populated with versions of this formula:

=IFERROR(IF(LEN(INDEX('AssocExecs master 3.5.xlsx'!Table1[#Data],MATCH(G12,'AssocExecs master 3.5.xlsx'!Table1[[ ORGANIZATION]],0),4))>0,"Yes","Error"),"No")

When the destination workbook is first opened, with the source workbook closed, the destination workbook shows a mix of "Yes" and "No" values, representing the last correct values when both workbooks were simultaneously open.

However, without ever opening the source workbook, the values returned by these formulas will all change to "No" after a few minutes (or some unrecognized event).

What might be happening?

Cheers,

Jay

first off you can:

Tools-Options, Edit tab.
Uncheck "Ask to automatically update links"

or when you open a file add

Please Login or Register  to view this content.

Then you can use the

Please Login or Register  to view this content.

convention to extract values from a closed workbook.

Thanks very much for you response.

To clarify, I should have mentioned that I'm using Excel 2007.

Also, regarding the 2nd question, I'm not looking to reference the source data w/o opening the source workbook ... I'm trying to understand why the values in the destination workbook change, even when the source workbook is not opened.

Cheers,

Jay

Darn, it just happened again. A whole column of "yes" and "no" converts to a column of only "no" -- apparently when I first recalculate the destination workbook.

The source workbook, however, was never opened!

More ideas, anyone?

Jay

Hi All !

I have the same issue than JayUSA, for the question one.
Here I have a long range of Excel files on a shared dropbox. So, when I write my formula and link the content of the main spreadsheet to other workbook, the source link automatically update but only for my computer. Here, I try again to remove the automatic update on the Edit links window to prevent this from happening.
I already have a small macro that only open and close all the relevant files with a refresh button to update.
Thus, I am still trying to remove the automatic update to manual update in the Edit links window, and want to us the refresh button only to update the data.

Thanks in advance,

Eshi