Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also from
India.

"Pritesh" wrote:

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

"Muhammed Rafeek M" wrote:

Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also from
India.

"Pritesh" wrote:

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

pls send me your work file to me:

you can use: =GETPIVOTDATA(........) function

"Pritesh" wrote:

Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

"Muhammed Rafeek M" wrote:

Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also from
India.

"Pritesh" wrote:

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for which I
use Sumif formula
(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open, alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

Sumif is among the functions that don't work on closed WBs, as is Indirect
and Countif and others.

One work-around is this *array* formula using the Sum() and IF()
combination:

=SUM(IF('C:\[Source.xls]Jun06'!$F1:$F1000=ExpCode,'C:\[Source.xls]Jun06'!$I1
:$I1000))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Also NOTE:
Array formulas *cannot* use entire column references ( F:F, I:I ).

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Pritesh" wrote in message
...
Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

"Muhammed Rafeek M" wrote:

Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also
from
India.

"Pritesh" wrote:

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group
and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for
which I
use Sumif formula

(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open,
alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

Hi RD,

I tried below suggested formula, which worked-out to discard "#Value" error.
Thanx.

"RagDyeR" wrote:

Sumif is among the functions that don't work on closed WBs, as is Indirect
and Countif and others.

One work-around is this *array* formula using the Sum() and IF()
combination:

=SUM(IF('C:\[Source.xls]Jun06'!$F1:$F1000=ExpCode,'C:\[Source.xls]Jun06'!$I1
:$I1000))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Also NOTE:
Array formulas *cannot* use entire column references ( F:F, I:I ).

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Pritesh" wrote in message
...
Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I use
Pivot Table, but, only when and where required.

"Muhammed Rafeek M" wrote:

Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also
from
India.

"Pritesh" wrote:

Hi, this is Pritesh from India. I work for a Pharma & Well Being Group
and
prepare monthly MIS reports in Excel. I have to link expense-codes and
relevant amounts to my file from a source file (both .xls files), for
which I
use Sumif formula

(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open,
alongwith
main file, the formula returns with "#Value" error. Can someone help me
discard "#Value" error.

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pritesh" wrote in message
...
Hi RD,

I tried below suggested formula, which worked-out to discard "#Value"
error.
Thanx.

"RagDyeR" wrote:

Sumif is among the functions that don't work on closed WBs, as is
Indirect
and Countif and others.

One work-around is this *array* formula using the Sum() and IF()
combination:

=SUM(IF('C:\[Source.xls]Jun06'!$F1:$F1000=ExpCode,'C:\[Source.xls]Jun06'!$I1
:$I1000))

--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

Also NOTE:
Array formulas *cannot* use entire column references ( F:F, I:I ).

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Pritesh" wrote in message
...
Hi Muhammed,

Thanx for your concern & response, but, you seem to be addictive to Pivot
Table, as, for both of my problems, you recommended me to use it. Even I
use
Pivot Table, but, only when and where required.

"Muhammed Rafeek M" wrote:

Hi
Better use pivot table. If you are not clear, send me mail to
with your problem files, i can guide you. I am also
from
India.

"Pritesh" wrote:

Hi, this is Pritesh from India. I work for a Pharma & Well Being
Group
and
prepare monthly MIS reports in Excel. I have to link expense-codes
and
relevant amounts to my file from a source file (both .xls files), for
which I
use Sumif formula

(=SUMIF('C:\[Source.xls]Jun06'!$F:$F,ExpCode,'C:\[Source.xls]Jun06'!$I:$I).
However, the problem is, unless I keep my source data file open,
alongwith
main file, the formula returns with "#Value" error. Can someone help
me
discard "#Value" error.