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.


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

You could try:
There maybe a better solution if you provide a few more details.

good luck

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


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:

Hope this helps.

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

Thsi is the formula

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


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

How about:

Hope that helps... Thsi is the formula