mperrah
11-06-2007, 01:23 AM
I use a sumproduct to populate a chart, if the date range chosen has no values or the start and stop dates are mismatched 27 cells fill with the ref! error.
how can i modify the formula to show " " if the cell has the ref! error.
I remember reading a thread about the code numbers the codes stand for,
I'll try to find it unless anyone has any easier way to hide the error...
I am familiar with the if statement, just not sure what the true state I'm testing for:
=if(SUMPRODUCT(--(item01=scan_item),-- _
(QCDate>=chStart),--(QCDate<=chEnd),-- _
(Techs=techChart))=REF!,"", _
(SUMPRODUCT(--(item01=scan_item),-- _
(QCDate>=chStart),--(QCDate<=chEnd),-- _
(Techs=techChart)))
what to replace the "REF!" with ?
Mark
how can i modify the formula to show " " if the cell has the ref! error.
I remember reading a thread about the code numbers the codes stand for,
I'll try to find it unless anyone has any easier way to hide the error...
I am familiar with the if statement, just not sure what the true state I'm testing for:
=if(SUMPRODUCT(--(item01=scan_item),-- _
(QCDate>=chStart),--(QCDate<=chEnd),-- _
(Techs=techChart))=REF!,"", _
(SUMPRODUCT(--(item01=scan_item),-- _
(QCDate>=chStart),--(QCDate<=chEnd),-- _
(Techs=techChart)))
what to replace the "REF!" with ?
Mark