Linked cells lose data when updated HELP!

[Guest]

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

 

[Tim879]

The #Value! error occurs when you use the wrong type of argument (i.e.
if you try to sum "abc").

You most likely have an error in one of your source files. Try opening
all of your source files and searching for #Value!. Alternatively,
select a cell with the #Value! error and use the Formula Auditing
Toolbar (View-> Toolbars -> Formula Auditing) to trace the error.

It may also help to check your links. You can also try to use the Edit-

Links and verify that each of the links is working.

 

[Guest]

so should have said that there are no value errors in B

 

[Guest]

Hi Tim,

Thank you for your reply
I didn't explain well enough I don't think.

If A = WrkSht 1 WrkBk 1
B = WrkSht 2 WrkBk 1
C = WrkSht 1 WrkBK 2

So A contains data in number format.
B contains function Countif summarising data for individual depot
C contains copy and pasted formula from B.

Formula works fine in B and copies to C but shows error message when updated.

I'm totally fuzzy on how the link works and what is "actually" stored in
each cell so I'm having trouble visualising what happens.

Any help appreciated
TIA

 

[Dave Peterson]

Jim Rech posted a registry tweak:
http://groups.google.com/[email protected]

 

[Guest]

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

 

[Dave Peterson]

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

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

 

[Guest]

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/com...0e95&mid=cae5b6fa-05a7-4d89-8a9d-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)

 

[Dave Peterson]

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.

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/com...0e95&mid=cae5b6fa-05a7-4d89-8a9d-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)

 

[Guest]

Will try that, thanks again :->
--
Smudge

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.