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
|
|||
|
|||
Excel formula giving #Value! error
I have a spreadsheet that has cells calculated from other
workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks |
#2
|
|||
|
|||
Some formulae require a referenced workbook to be open. What formulae
are giving you the error? In article , wrote: I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? |
#3
|
|||
|
|||
You're probably using a function in your formulas that doesn't work on
closed WBs, like INDIRECT() for example. Post the formulas you're having a problem with, together with the situation your using them in, so perhaps an alternative might be possibly suggested. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks |
#4
|
|||
|
|||
Hi
Some functions (like INDIRECT) don't work with links to closed workbook as parameters. Use mirror sheet(s), where source data are mirrored using simple links or formulas like =IF(MyLink="","",MyLink) and refer to mirrored data instead original workbooks. Arvi laanemets wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks |
#5
|
|||
|
|||
I am using SUMIF where both the range and the values come
from another spreadsheet. -----Original Message----- You're probably using a function in your formulas that doesn't work on closed WBs, like INDIRECT() for example. Post the formulas you're having a problem with, together with the situation your using them in, so perhaps an alternative might be possibly suggested. -- Regards, RD ---------------------------------------------------------- ---------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------- ---------- wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks . |
#6
|
|||
|
|||
I am using SUMIF....how and where do I use mirror links?
Could you please elaborate? -----Original Message----- Hi Some functions (like INDIRECT) don't work with links to closed workbook as parameters. Use mirror sheet(s), where source data are mirrored using simple links or formulas like =IF(MyLink="","",MyLink) and refer to mirrored data instead original workbooks. Arvi laanemets wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks . |
#7
|
|||
|
|||
That's also one that doesn't work with closed WBs.
But there are work-arounds for that ... i.e. =SUM(IF(... So, post some of your problem formulas. -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- wrote in message ... I am using SUMIF where both the range and the values come from another spreadsheet. -----Original Message----- You're probably using a function in your formulas that doesn't work on closed WBs, like INDIRECT() for example. Post the formulas you're having a problem with, together with the situation your using them in, so perhaps an alternative might be possibly suggested. -- Regards, RD ---------------------------------------------------------- ---------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------- ---------- wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks . |
#8
|
|||
|
|||
Hi
Let it be you have in a workbook (placed in My Documents folder on C drive) MySource on sheet Sheet1 a table Name Amount John 1000 Mary 1200 Bill 800 John 500 Bill 1100 .... In workbook MyTarget you have the list of names, and you want sum amounts from MySource for every name. Create in MyTarget a sheet MyMirror; Into cell A1 on sheet MyMirror enter the formula =IF('C:\My Documents\[MySource.xls]Sheet1'!A1="","",'C:\My Documents\[MySource.xls]Sheet1'!A1) and copy it p.e. to range A1:B100 (at least same number of rows as has table in MySource) On sheet with name list (with names in column A, started from row 2), enter p.e. into cell B2 the formula =SUMIF(MyMirror!$A$2:$A$100,A2,MyMirror!$B$2:$B$10 0) and copy it down. Arvi Laanemets wrote in message ... I am using SUMIF....how and where do I use mirror links? Could you please elaborate? -----Original Message----- Hi Some functions (like INDIRECT) don't work with links to closed workbook as parameters. Use mirror sheet(s), where source data are mirrored using simple links or formulas like =IF(MyLink="","",MyLink) and refer to mirrored data instead original workbooks. Arvi laanemets wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks . |
#9
|
|||
|
|||
Thank you much.
-----Original Message----- Hi Let it be you have in a workbook (placed in My Documents folder on C drive) MySource on sheet Sheet1 a table Name Amount John 1000 Mary 1200 Bill 800 John 500 Bill 1100 .... In workbook MyTarget you have the list of names, and you want sum amounts from MySource for every name. Create in MyTarget a sheet MyMirror; Into cell A1 on sheet MyMirror enter the formula =IF('C:\My Documents\[MySource.xls]Sheet1'!A1="","",'C:\My Documents\[MySource.xls]Sheet1'!A1) and copy it p.e. to range A1:B100 (at least same number of rows as has table in MySource) On sheet with name list (with names in column A, started from row 2), enter p.e. into cell B2 the formula =SUMIF(MyMirror!$A$2:$A$100,A2,MyMirror!$B$2:$B$1 00) and copy it down. Arvi Laanemets wrote in message ... I am using SUMIF....how and where do I use mirror links? Could you please elaborate? -----Original Message----- Hi Some functions (like INDIRECT) don't work with links to closed workbook as parameters. Use mirror sheet(s), where source data are mirrored using simple links or formulas like =IF(MyLink="","",MyLink) and refer to mirrored data instead original workbooks. Arvi laanemets wrote in message ... I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks . . |
#10
|
|||
|
|||
Hi,
Does this apply to OFFSET too? Regards, Vinaya. Arvi Laanemets wrote: Hi Let it be you have in a workbook (placed in My Documents folder on C drive) MySource on sheet Sheet1 a table Name Amount John 1000 Mary 1200 Bill 800 John 500 Bill 1100 ... In workbook MyTarget you have the list of names, and you want sum amounts from MySource for every name. Create in MyTarget a sheet MyMirror; Into cell A1 on sheet MyMirror enter the formula =IF('C:\My Documents\[MySource.xls]Sheet1'!A1="","",'C:\My Documents\[MySource.xls]Sheet1'!A1) and copy it p.e. to range A1:B100 (at least same number of rows as has table in MySource) On sheet with name list (with names in column A, started from row 2), enter p.e. into cell B2 the formula =SUMIF(MyMirror!$A$2:$A$100,A2,MyMirror!$B$2:$B$10 0) and copy it down. Arvi Laanemets wrote in message ... I am using SUMIF....how and where do I use mirror links? Could you please elaborate? -----Original Message----- Hi Some functions (like INDIRECT) don't work with links to closed workbook as parameters. Use mirror sheet(s), where source data are mirrored using simple links or formulas like =IF(MyLink="","",MyLink) and refer to mirrored data instead original workbooks. Arvi laanemets wrote in message .. . I have a spreadsheet that has cells calculated from other workbooks. Some cells are updated while others give me an error of #Value!. The error goes away when I open the other spreadhsheet. What do I do? Thanks . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I get around Excel formula is too long error message? | Chris | General Discussion | 1 | September 7th, 2004 06:53 PM |
Error Message for Excel and Word | Dakatsu | General Discussions | 1 | August 27th, 2004 09:35 AM |
Productkey problem when installing office 2003 on network | Stefan Schreurs | Setup, Installing & Configuration | 1 | June 1st, 2004 11:16 PM |
LARGE Formula giving #DIV/0! | PLN | Worksheet Functions | 4 | February 12th, 2004 10:47 PM |