There are some functions that won't work when the sending workbook is closed:
=indirect(), =sumif(), =countif() are a few

For the last two, you may find that you can reenter the formula as an array
formula (you can't use complete columns until xl2007, though) like:

=sum(if(...))
Entered with ctrl-shift-enter

or replace it with an equivalent =sumproduct() formula.

=======
Sometimes breaking links breaks the functionality of the workbook. You may not
always want to do this.

Smudge wrote:

Hi,

I've got sorted, non-techy route (will work on that but baby steps for now).
I searched previous posts and found this
http://www.microsoft.com/office/comm...d-ba4a7527e33b
(hoping this is right link) Why will links not updqate unless source is open?
The problem was that countif needs both source and destination files to be
opened.

So I opened all files (50) and links restored then I copied wrkbook and
Edit Links Startup Prompt selected Don't display alert and don't update
external links as a backup. Then in original broke links in the Edit Links
menu.

Stupidly pleased with myself for sorting it out but it's a fantastic feeling
when you work it out and learn something new.

Thanks for help and I have saved Dave Peterson's link for the future
(learning more all the time and I will get to a stage where I'm brave enough
for the techy stuff)
--
Smudge

"Dave Peterson" wrote:

Print Jim's response and ask one of your pc savvy co-workers (if you're at
work)--or even IT staff for help.

Smudge wrote:

Thanks for reply. Too advanced for me though!
Thanks again
--
Smudge

"Dave Peterson" wrote:

Jim Rech posted a registry tweak:
http://groups.google.com/groups?thre...GP11.phx .gbl

Smudge wrote:

Hi everyone,

Excel 2003 Windows XP

I really need help.

Over the last couple of days I've been copying cells from several workbooks
to a summary workbook. The cells from the source workbooks pick up data from
individual worksheets using a countif function.

all well and good until I re-open the summary workbook and it asks if I want
to update or not. Tried updating once and got VALUE in all of the copied
cells so quit witout saving and opened without updating.Fine
Then decided that Excel wouldn't beat me and tried altering the update links
bits in Tools Options but should have put brain in gear first. I removed tick
from ask to update external links and also save external link values so when
i saved and opened it open and I think it updated links automatically and I
hadn't made a back up.

Please, please can someone tell me how to make the values come back instead
of the nightmarish #VALUE.

If it helps to know the source and summary workbooks are both on the same
server but in different folders.

TIA
Smudge
--
Smudge

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson