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

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?

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

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

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


.

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


.

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


.

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


.

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


.



.

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


.