Thread: Remove unwanted cells

Hello,

I have an excel file with over 10,000 rows, so I need a quick way to do this.

I have to convert one column in x currency into y currency.
When I apply the formula to the first row, I drag it down to apply it to the 10,000 other rows.

However, when I do this I get the #value! where there is a gap in the rows of data, because there is no data entry in the fields specified by the formula.

Is there a quick way to either remove the #value! cells or to apply the formula all the way down 10,000 rows without #value! showing up where there is no data to work from.

The attached spreadsheet should show what I mean.

Any help would be greatly appreciated.

Cian

C-Shore,

The common method to prevent errors when necessary cells are blank is to use an IF statement to check the cell. Using your sample workbook, in cell E4, you can use the following formula:
=IF(D4="","",D4/0.85)

and then copy down. The same formula works for column H as well. Attached is a modified version of your worksheet to show the results.

Hope that helps,
~tigeravatar

Originally Posted by tigeravatar

C-Shore,

The common method to prevent errors when necessary cells are blank is to use an IF statement to check the cell. Using your sample workbook, in cell E4, you can use the following formula:
=IF(D4="","",D4/0.85)

and then copy down. The same formula works for column H as well. Attached is a modified version of your worksheet to show the results.

Hope that helps,
~tigeravatar

Hi Tigeravatar,

Thanks a million for that.
When I used IF before, my logical value was down as =0, so putting it at "" has sorted it.

Thank you so much!

C-Shore