Adding a formula that doesn't give a #VALUE! error

[Maury Markowitz]

I am working with a large spreadsheet, typically about 2000 rows and
100 or more columns. On the far right, hidden, are a number of columns
that contain input data, with many blank cells. On the left is the
"user area", where formulas combine values from the input data into
user-friendly values.

The problem I'm having is that the formulas return #VALUE! when the
input cells are empty - and will remain so. Yes, I know I can turn
this warning off, but I don't want to, because it's a global setting
and both that user and other sheets I make need to have it on. So I
want to fix the problem.

I tried looping over the cells looking for ISERROR and emptying them
out, but this turned out to be VERY slow - about 25% of the time
needed to prepare the entire sheet! Then I tried copy/paste
SkipEmptyRows, but of course the rows aren't actually empty. Is there
a way to tell it to skip rows with errors? Or perhaps some other way
to fix them?

Maury

 

[Roland]

If you are using a macro to do this you can use if statements for the formula
to not include if the cell value is empty.

Or maybe you can use =iferror

 

[Maury Markowitz]

Both of those would work, but make the formula much more difficult to
read.

Is there some sort of "conditional find" that would allow me to find/
replace on the errors?

Maury