Excel formula giving #Value! error

[Guest]

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

 

[JE McGimpsey]

Some formulae require a referenced workbook to be open. What formulae
are giving you the error?

 

[RagDyeR]

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 !
--------------------------------------------------------------------

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

 

[Arvi Laanemets]

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

 

[Guest]

I am using SUMIF where both the range and the values come
from another spreadsheet.

 

[Guest]

I am using SUMIF....how and where do I use mirror links?
Could you please elaborate?

 

[RagDyeR]

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.

 

[Arvi Laanemets]

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$100)
and copy it down.

Arvi Laanemets

 

[Guest]

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$100)
and copy it down.

Arvi Laanemets

I am using SUMIF....how and where do I use mirror links?
Could you please elaborate? me
an

.

 

[vinaya nayak]

Hi,

Does this apply to OFFSET too?

Regards,
Vinaya.

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$100)
and copy it down.

Arvi Laanemets

I am using SUMIF....how and where do I use mirror links?
Could you please elaborate?


closed workbook as


mirrored using simple