MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Clearing #Value!


Posted by Roy Brunt on August 17, 2001 10:04 AM

I have autofilled a formula down a row of cells. Where there is no data for the formula yet, i get the #Value! returned. Is there a way to stop this showing in these cells without affecting the formula in the cells.

Thanks
Roy


Posted by IML on August 17, 2001 10:21 AM

You could try:
=IF(ISNUMBER(yourformula),yourformula,"")
There maybe a better solution if you provide a few more details.

good luck

Posted by Dexter on August 17, 2001 10:27 AM

Roy,

You can modify your formula to return a blank instead of the #Value! If you have text in cell D12 and your formula is set to use that cell in a formula like D12 * C12 than use this formula:

=IF(ISNUMBER(D12),D12*C12,"")
Hope this helps.

Posted by Roy on August 17, 2001 10:30 AM

Thsi is the formula
=LEFT(B7,SEARCH("(",B7)-1)

if there is nothing in b7 i get the error. Your solution didn't work m8. Is there a way for the cell to remain blank until i put something into cell b7.

Thanks for the help

Roy

Posted by IML on August 17, 2001 11:15 AM

How about:
=IF(ISBLANK(B7),"",LEFT(B7,SEARCH("(",B7)-1))

Hope that helps... Thsi is the formula