VALUE MESSAGE

[OZZIE]

I have a spreadsheet which I report the monthly results.
In that spreadsheet I link to another spreadsheet which
recaps the A/R and days outstanding. I have a cell with
the following formula in it:

=ROUND(SUMIF('[05-04 MO END.xls]Aging Detail By Call Rep'!
$C$7:$G$18,"<0")/1000,0)

If I open the monthly results without the other file, I
get the #VALUE#.

Any ideas why?

Appreciate the help/feedback

 

[Frank Kabel]

Hi
SUMIF does not work on closed files. Try changing the formula to:
=ROUND(SUMPRODUCT(--('[05-04 MO END.xls]Aging Detail By Call Rep'!
$C$7:$G$18<0),'[05-04 MO END.xls]Aging Detail By Call Rep'!
$C$7:$G$18)/1000,0)

 

[OZZIE]

Hats off to you FK!! Thanks for the help, works
beautifully (and thanks for reminding me I'm not as smart
as I thought I was - but I was smart enough to ask for
help...)

-----Original Message-----
Hi
SUMIF does not work on closed files. Try changing the formula to:
=ROUND(SUMPRODUCT(--('[05-04 MO END.xls]Aging Detail By

Call Rep'! $C$7:$G$18<0),'[05-04 MO END.xls]Aging Detail
By Call Rep'!$C$7:$G$18)/1000,0)

--
Regards
Frank Kabel
Frankfurt, Germany

I have a spreadsheet which I report the monthly results.
In that spreadsheet I link to another spreadsheet which
recaps the A/R and days outstanding. I have a cell with
the following formula in it:

=ROUND(SUMIF('[05-04 MO END.xls]Aging Detail By Call Rep'!
$C$7:$G$18,"<0")/1000,0)

If I open the monthly results without the other file, I
get the #VALUE#.

Any ideas why?

Appreciate the help/feedback

.

 

[Peo Sjoblom]

SUMIF won't work if the linked workbook is closed
you can use SUMPRODUCT instead, this should replace the sumif part

=SUMPRODUCT(--('[05-04 MO END.xls]Aging Detail By Call
Rep'!$C$7:$G$18<0),'[05-04 MO END.xls]Aging Detail By Call Rep'!$C$7:$G$18)

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Ozzie]

Peo - thanks!! It works.

-----Original Message-----
SUMIF won't work if the linked workbook is closed
you can use SUMPRODUCT instead, this should replace the sumif part

=SUMPRODUCT(--('[05-04 MO END.xls]Aging Detail By Call
Rep'!$C$7:$G$18<0),'[05-04 MO END.xls]Aging Detail By Call Rep'!$C$7:$G$18)

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

"OZZIE"

message news:[email protected]...

I have a spreadsheet which I report the monthly results.
In that spreadsheet I link to another spreadsheet which
recaps the A/R and days outstanding. I have a cell with
the following formula in it:

=ROUND(SUMIF('[05-04 MO END.xls]Aging Detail By Call Rep'!
$C$7:$G$18,"<0")/1000,0)

If I open the monthly results without the other file, I
get the #VALUE#.

Any ideas why?

Appreciate the help/feedback

.