Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default Linked cells lose data when updated HELP!

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 208
Default Linked cells lose data when updated HELP!

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.




On Aug 30, 3:00 pm, 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default Linked cells lose data when updated HELP!

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


"Tim879" wrote:

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.




On Aug 30, 3:00 pm, 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




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default Linked cells lose data when updated HELP!

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
--
Smudge (dazed and confused)


"Tim879" wrote:

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.




On Aug 30, 3:00 pm, 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




  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Linked cells lose data when updated HELP!

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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default Linked cells lose data when updated HELP!

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Linked cells lose data when updated HELP!

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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default Linked cells lose data when updated HELP!

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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Linked cells lose data when updated HELP!

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
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 53
Default Linked cells lose data when updated HELP!

Will try that, thanks again :-
--
Smudge


"Dave Peterson" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can a linked hyperlink be updated Tgivin Excel Worksheet Functions 0 April 12th 06 08:50 PM
In a linked calculation how do I lose the '0' in the linked file? Anita Excel Worksheet Functions 2 February 3rd 06 02:54 PM
hOW CAN LINKED FILE DATA CAN BE UPDATED AT ONCE Excel User Excel Discussion (Misc queries) 2 August 15th 05 01:33 PM
Pivot Tables lose some formatting when Updated Jugglertwo Excel Discussion (Misc queries) 1 March 23rd 05 01:48 AM
cells lose relativity to other WorkSheets after data sort? Ed Murray - ALPCO Excel Worksheet Functions 1 November 8th 04 08:02 PM


All times are GMT +1. The time now is 06:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"