If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
A link between two workbooks doesn't keep the value, shows #value!
Same problem.
Formulas based on cells from other workbooks, doesn’t work unless source file is opened. I don’t 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
|
|||
|
|||
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
|
|||
|
|||
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) 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 |
#5
|
|||
|
|||
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
|
|||
|
|||
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) 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 |
#7
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|