VALUE MESSAGE

  • Thread starter Thread starter OZZIE
  • Start date Start date
O

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
 
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)
 
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
Click to expand...
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
Click to expand...
.
Click to expand...
 
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
 
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"
Click to expand...
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
Click to expand...


.
Click to expand...
 
Back
Top