Removing the #value return in a formula

PeterDavids

New Member
Joined
Apr 18, 2013
Messages
11
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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
11
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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