External Links

[Bill]

Hello,

I'm running Excel 2002. I have a spreadsheet with external references.
These references are established through Names (Insert > Names > Define).
As I copy and paste cells with formulas containing those names, I see "Link"
in the status bar and have to wait for the update to complete. I want to
update them manually as the update takes a rather long time to complete. I
suppose I would like to "work offline" until I'm ready to update the links.

I go to Edit > Links... and I see the list of external references. I have
the Startup Prompt set to the second radio, "Don't display the alert and
don't update automatic links." Immediately above that button in the lower
left is:

Update: O Automatic O Manual

The "Automatic" button is selected and the "Manual" but is greyed out so
that I can not select it. Does anyone know why it might be greyed out, and
how I can select "Manual" here?

Bill

 

[Guest]

Turn your calculation setting to Manual (tools-->options-->calculation=manual).

don't forget to turn it back on when your done.

----- Bill wrote: -----

Hello,

I'm running Excel 2002. I have a spreadsheet with external references.
These references are established through Names (Insert > Names > Define).
As I copy and paste cells with formulas containing those names, I see "Link"
in the status bar and have to wait for the update to complete. I want to
update them manually as the update takes a rather long time to complete. I
suppose I would like to "work offline" until I'm ready to update the links.

I go to Edit > Links... and I see the list of external references. I have
the Startup Prompt set to the second radio, "Don't display the alert and
don't update automatic links." Immediately above that button in the lower
left is:

Update: O Automatic O Manual

The "Automatic" button is selected and the "Manual" but is greyed out so
that I can not select it. Does anyone know why it might be greyed out, and
how I can select "Manual" here?

Bill

 

[Bill]

When I do that I am still not able to select "Manual" in the Edit>Links...
window. I also deselect "Update external references" and no difference.

 

[Dave Peterson]

I think that those automatic/manual option buttons are for OLE links (other
programs)--not links to excel.

Didn't changing the startup prompt do what you wanted?

(It worked fine for me in light testing.)

 

[Bill]

Thanks for the info about OLE links.

Changing the startup prompt does just what it advertises, but that only
applies to startup, right? Anyway, the answer is No, it didn't do what I
wanted. By the way, I'm doing the copy-paste within a macro, fwiw. I'm
copying from one workbook to another. And I have ensured that both source
and destination workbooks have the startup prompt and manual calculation
choices set as discussed in this thread.

 

[Dave Peterson]

I'm confused.

You're copying a range from one workbook to another workbook. And the range
you're copying has links to a third workbook?

In more light testing:

If calculation were set to automatic, then the pasted range (with the new links)
retrieved the current values from the original workbook.

If calculation were set to manual, then the pasted range just showed the same
values as the range that was copied.

But as soon as I calculated, those links wanted to be updated.

I could play some games to stop it:

Leave calculation set to manual--but make sure "recalculate before saving" is
unchecked--also under Tools|Options|calculation tab.

Then save the workbook with the copied range, close that workbook, and reopen it
so the update links option can take effect.

Sounds like a lot of work--but it might be worth it to you. (I didn't see
another way.)