Removing the #value return in a formula

[PeterDavids]

Hi...

Please would anyone be able to assist in solving a pesky problem that I can't seem to master?

My formula is sum(A7:Q7)-H7 which returns a #value;

In which H7 is =VLOOKUP(A7,'[ATN Colesberg Timesheet.xlsm]APR 2013'!$B$8:$CJ$57,6,FALSE)

Will really appreciate the help!

Thanks
Peter

 

[jacqueslegrand]

If your value error comes from the H7 cell, maybe just do the trick like that :
=sum(A7:P7,R7:Q7)

 

[jarjarbingie]

if any of the values is an error, SUM would definitely be an error.. try =SUMIF(A7:Q7,"<>#VALUE!")..

 

[aequitas1903]

Hi Peter,

Problem is because of H7 as jacqueslegrand said. H7 cell does not contain a numerical value.

If H7 must have a numerical value you should check H7 or lets say your formula is in cell R7 write this =IF(ISERROR(H7);"Check h7";H7) than you can check find the problem.

If H7 doesnt have to have a numerival value and you want a value or something and easy iferror will solve your problem. =IFERROR(H7;"N/A")

Please let me know if you need any further assitance.

 

[PeterDavids]

Thanks Jacques

Maybe i should provide a little more clarity.

The contents of the range A7 to Q7 is also a return of the formula =VLOOKUP(A7,'[ATN Colesberg Timesheet.xlsm]APR 2013'!$B$8:$CJ$57,6,FALSE)
where the 6 in the formula is replaced with relevant column numbers of the target spread sheet,

the formula works fine when the return of the above formula has a value in it but as soon as it contains no value, i get the error message.

Does this help a bit more?