Removing the #value return in a formula

PeterDavids

New Member
Joined
Apr 18, 2013
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

jarjarbingie

Well-known Member
Joined
Nov 15, 2012
Messages
607
if any of the values is an error, SUM would definitely be an error.. try =SUMIF(A7:Q7,"<>#VALUE!")..
 

aequitas1903

Board Regular
Joined
Mar 8, 2012
Messages
127
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

New Member
Joined
Apr 18, 2013
Messages
14
Office Version
  1. 2019
Platform
  1. Windows
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,428
Messages
5,837,177
Members
430,481
Latest member
nizambakhshi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top