Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 27
Default A link between two workbooks doesn't keep the value, shows #value!

I have a cell linked to another one in a second workbook. It doesn't show the
value from the origin unless open. Instead it shows #value! error. When I
open the origin then it shows the value again. If I close the origin the link
keeps the value while the destination is open. When closing and then opening
the destination it shows the #value! error again.

It doesn't happen to every link just to some of them
  #2   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 1
Default A link between two workbooks doesn't keep the value, shows #value!

Same problem.
Formulas based on cells from other workbooks, doesnt work unless source
file is opened. I dont know how to make Excel 2007 to read values of cells
from other excel filles without reopen it (that source file).
Any idea?

Is there solution to get values from workbook external links in Excel 2007?


"Gerardo" wrote:

I have a cell linked to another one in a second workbook. It doesn't show the
value from the origin unless open. Instead it shows #value! error. When I
open the origin then it shows the value again. If I close the origin the link
keeps the value while the destination is open. When closing and then opening
the destination it shows the #value! error again.

It doesn't happen to every link just to some of them

  #3   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default A link between two workbooks doesn't keep the value, shows #value!

Some links work, don't they?
Are the ones that don't work more complicated formulas (e.g. VLOOKUP)?
Some formulas are just too difficult for Excel to compute without
having the source workbook open.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #4   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 1
Default A link between two workbooks doesn't keep the value, shows #value

Right, pure links works; but if you try to use that link in formulas (my case
sumif) doesnt work anymore until you open source file too.
I think problems came from translation (composing) path.

"Bill Manville" wrote:

Some links work, don't they?
Are the ones that don't work more complicated formulas (e.g. VLOOKUP)?
Some formulas are just too difficult for Excel to compute without
having the source workbook open.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #5   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default A link between two workbooks doesn't keep the value, shows #value

As I said, some formulas are just too complicated for Excel to compute
on a closed file.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



  #6   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 35,218
Default A link between two workbooks doesn't keep the value, shows #value

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

You could replace the =sumif() formula with the equivalent =sumproduct()

=sumif(a:a,"x",b:b)
could be replaced with
=sumproduct(--(a:a="x"),(b:b))

And because you're working in xl2007, you can use the whole column.

.....

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Liviu Dinu wrote:

Right, pure links works; but if you try to use that link in formulas (my case
sumif) doesnt work anymore until you open source file too.
I think problems came from translation (composing) path.

"Bill Manville" wrote:

Some links work, don't they?
Are the ones that don't work more complicated formulas (e.g. VLOOKUP)?
Some formulas are just too difficult for Excel to compute without
having the source workbook open.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 39
Default A link between two workbooks doesn't keep the value, shows #va



"Dave Peterson" wrote:

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

You could replace the =sumif() formula with the equivalent =sumproduct()

=sumif(a:a,"x",b:b)
could be replaced with
=sumproduct(--(a:a="x"),(b:b))

And because you're working in xl2007, you can use the whole column.

.....

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

Liviu Dinu wrote:

Right, pure links works; but if you try to use that link in formulas (my case
sumif) doesn€„¢t work anymore until you open source file too.
I think problems came from translation (composing) path.

"Bill Manville" wrote:

Some links work, don't they?
Are the ones that don't work more complicated formulas (e.g. VLOOKUP)?
Some formulas are just too difficult for Excel to compute without
having the source workbook open.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup



--

Dave Peterson

  #8   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 39
Default A link between two workbooks doesn't keep the value, shows #va

I guess my big question here is why did this appear to work in Excel 2003,
but not now in Excel 2007? It used to be that users would elect not to
update the links and then in the spreadsheet they received via e-mail, it
would have the data still contained within the spreadsheet--though it was
stale, at least it was there.

Now in Outlook 2007, it appears as if the data is no longer available unless
it can update the links OR unless I save the file locally, open excel by
itself, turn calculation of formulas to manual, then open the spreadsheet
from within Excel 2007. Any thoughts on this and how to get it to function
as it did in Excel 2003?

Much appreciated!
Lance
  #9   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 35,218
Default A link between two workbooks doesn't keep the value, shows #va

The =sumif() formula doesn't work in any version of excel if the sending
workbook is closed.

But the way the cells with links are displayed if the links aren't updated did
change (I think with xl2k).

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

You'll have to change the version number in Jim's post.

Lance wrote:

I guess my big question here is why did this appear to work in Excel 2003,
but not now in Excel 2007? It used to be that users would elect not to
update the links and then in the spreadsheet they received via e-mail, it
would have the data still contained within the spreadsheet--though it was
stale, at least it was there.

Now in Outlook 2007, it appears as if the data is no longer available unless
it can update the links OR unless I save the file locally, open excel by
itself, turn calculation of formulas to manual, then open the spreadsheet
from within Excel 2007. Any thoughts on this and how to get it to function
as it did in Excel 2003?

Much appreciated!
Lance


--

Dave Peterson
  #10   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 39
Default A link between two workbooks doesn't keep the value, shows #va

Thanks for the information, but I do question it a little in that if I open
the same spreadsheet in Excel 2003, all of the cells with =sumif() formulas
in them contain valid data results as I would expect (even though I don't
have access to the source spreadsheet in the formulas link). In 2007
however, I can only get those results if automatic calculation is turned off.

"Dave Peterson" wrote:

The =sumif() formula doesn't work in any version of excel if the sending
workbook is closed.

But the way the cells with links are displayed if the links aren't updated did
change (I think with xl2k).

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

You'll have to change the version number in Jim's post.

Lance wrote:

I guess my big question here is why did this appear to work in Excel 2003,
but not now in Excel 2007? It used to be that users would elect not to
update the links and then in the spreadsheet they received via e-mail, it
would have the data still contained within the spreadsheet--though it was
stale, at least it was there.

Now in Outlook 2007, it appears as if the data is no longer available unless
it can update the links OR unless I save the file locally, open excel by
itself, turn calculation of formulas to manual, then open the spreadsheet
from within Excel 2007. Any thoughts on this and how to get it to function
as it did in Excel 2003?

Much appreciated!
Lance


--

Dave Peterson



  #11   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 35,218
Default A link between two workbooks doesn't keep the value, shows #va

Did you try changing the windows registry setting that Jim described in his
post?

Lance wrote:

Thanks for the information, but I do question it a little in that if I open
the same spreadsheet in Excel 2003, all of the cells with =sumif() formulas
in them contain valid data results as I would expect (even though I don't
have access to the source spreadsheet in the formulas link). In 2007
however, I can only get those results if automatic calculation is turned off.

"Dave Peterson" wrote:

The =sumif() formula doesn't work in any version of excel if the sending
workbook is closed.

But the way the cells with links are displayed if the links aren't updated did
change (I think with xl2k).

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

You'll have to change the version number in Jim's post.

Lance wrote:

I guess my big question here is why did this appear to work in Excel 2003,
but not now in Excel 2007? It used to be that users would elect not to
update the links and then in the spreadsheet they received via e-mail, it
would have the data still contained within the spreadsheet--though it was
stale, at least it was there.

Now in Outlook 2007, it appears as if the data is no longer available unless
it can update the links OR unless I save the file locally, open excel by
itself, turn calculation of formulas to manual, then open the spreadsheet
from within Excel 2007. Any thoughts on this and how to get it to function
as it did in Excel 2003?

Much appreciated!
Lance


--

Dave Peterson


--

Dave Peterson
  #12   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 39
Default A link between two workbooks doesn't keep the value, shows #va

Dave,

I actually did try the registry hack described by Jim and it did appear to
work, but deploying that to the masses would be difficult. That being said,
I opened up a case with Microsoft on this and the lady I spoke with said that
the registry change can work, but they have also seen it cause issues with
mis-calculating some formulas.

She said that the reason it's doing what it is can be explained in that the
spreadsheet with the links in it was created and saved in Excel 2003 and then
when an Excel 2007 client opens it, it sees that it was created in a
different version and immediately wants to update all of the links and
calculations (even if you tell it not to).

So unfortunately, this is by design, but breaking the link before sending is
one option to get around the issue (along with the registry change and/or a
copy and paste special). Also, I believe if the person who saves is is using
Excel 2007, the issue will be gone for other Excel 2007 clients.

Supporting document:
925893 External links may be updated in Excel 2007 when you open a workbook
that was last saved in an earlier version of Excel
http://support.microsoft.com/default...b;EN-US;925893

Lance

"Dave Peterson" wrote:

Did you try changing the windows registry setting that Jim described in his
post?

Lance wrote:

Thanks for the information, but I do question it a little in that if I open
the same spreadsheet in Excel 2003, all of the cells with =sumif() formulas
in them contain valid data results as I would expect (even though I don't
have access to the source spreadsheet in the formulas link). In 2007
however, I can only get those results if automatic calculation is turned off.

"Dave Peterson" wrote:

The =sumif() formula doesn't work in any version of excel if the sending
workbook is closed.

But the way the cells with links are displayed if the links aren't updated did
change (I think with xl2k).

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

You'll have to change the version number in Jim's post.

Lance wrote:

I guess my big question here is why did this appear to work in Excel 2003,
but not now in Excel 2007? It used to be that users would elect not to
update the links and then in the spreadsheet they received via e-mail, it
would have the data still contained within the spreadsheet--though it was
stale, at least it was there.

Now in Outlook 2007, it appears as if the data is no longer available unless
it can update the links OR unless I save the file locally, open excel by
itself, turn calculation of formulas to manual, then open the spreadsheet
from within Excel 2007. Any thoughts on this and how to get it to function
as it did in Excel 2003?

Much appreciated!
Lance

--

Dave Peterson


--

Dave Peterson

  #13   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 1
Default AW: A link between two workbooks doesn't keep the value, shows #va

There's a free solution for getting updated data from CLOSED
XLS-FILES!!!!!!!!!!!!

You can (like I did for my company) download freeware (virus-free!!!),
which adds about 85 new functions to your excel. I had my problem
fixed...

URL:

http://xcell05.free.fr/english/

Read it, & enjoy!

Greetz,

Patrick


Lance:
Dave,

I actually did try the registry hack described by Jim and it did

appear to
work, but deploying that to the masses would be difficult. That being

said,
I opened up a case with Microsoft on this and the lady I spoke with

said that
the registry change can work, but they have also seen it cause issues

with
mis-calculating some formulas.

She said that the reason it's doing what it is can be explained in

that the
spreadsheet with the links in it was created and saved in Excel 2003

and then
when an Excel 2007 client opens it, it sees that it was created in a
different version and immediately wants to update all of the links and


calculations (even if you tell it not to).

So unfortunately, this is by design, but breaking the link before

sending is
one option to get around the issue (along with the registry change

and/or a
copy and paste special). Also, I believe if the person who saves is

is using
Excel 2007, the issue will be gone for other Excel 2007 clients.

Supporting document:
925893 External links may be updated in Excel 2007 when you open a

workbook
that was last saved in an earlier version of Excel
http://support.microsoft.com/default...b;EN-US;925893

Lance

"Dave Peterson" wrote:

Did you try changing the windows registry setting that Jim described

in his
post?

Lance wrote:

Thanks for the information, but I do question it a little in that

if I open
the same spreadsheet in Excel 2003, all of the cells with =sumif()

formulas
in them contain valid data results as I would expect (even though I

don't
have access to the source spreadsheet in the formulas link). In

2007
however, I can only get those results if automatic calculation is

turned off.

"Dave Peterson" wrote:

The =sumif() formula doesn't work in any version of excel if the

sending
workbook is closed.

But the way the cells with links are displayed if the links

aren't updated did
change (I think with xl2k).

Jim Rech posted a registry tweak:

http://groups.google.com/groups?thre...GP11.phx .gbl

You'll have to change the version number in Jim's post.

Lance wrote:

I guess my big question here is why did this appear to work in

Excel 2003,
but not now in Excel 2007? It used to be that users would

elect not to
update the links and then in the spreadsheet they received via

e-mail, it
would have the data still contained within the

spreadsheet--though it was
stale, at least it was there.

Now in Outlook 2007, it appears as if the data is no longer

available unless
it can update the links OR unless I save the file locally,

open excel by
itself, turn calculation of formulas to manual, then open the

spreadsheet
from within Excel 2007. Any thoughts on this and how to get

it to function
as it did in Excel 2003?

Much appreciated!
Lance

--

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
Link malfunction-shows path instead of cell content Twishlist Excel Worksheet Functions 4 May 23rd 07 03:09 AM
Excel link shows zero value instead of actual amt Deb Excel Discussion (Misc queries) 1 October 30th 06 04:41 PM
Paste Link shows '0' for empty cells deluth Excel Discussion (Misc queries) 2 September 7th 06 08:33 PM
Link to cell shows 0 value tanyhart Excel Discussion (Misc queries) 3 July 20th 06 04:16 PM
External Link Shows Formula Not Info nia Excel Worksheet Functions 3 December 4th 05 03:30 AM


All times are GMT +1. The time now is 10:06 AM.

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"