Custom Format N/A #DIV/O!
 

I would like to know if there is a custom format that will hide formula
errors. I'm not talking about conditional formatting.

Example:

This will hide Zero's

0;-0;;@

This will hide the The Bignum (E+307)

[=9.99999999999999E+307]"";General



But what will hide

#N/A and #DIV/O!

What do you think*?*


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=562310

Custom Format N/A #DIV/O!
 

"vane0326" wrote in
message ...

I would like to know if there is a custom format that will hide formula
errors. I'm not talking about conditional formatting.

Example:

This will hide Zero's

0;-0;;@

This will hide the The Bignum (E+307)

[=9.99999999999999E+307]"";General



But what will hide

#N/A and #DIV/O!

What do you think*?*


--
vane0326

Hi!

I'm gonna take a guess and say that is not possible. Formats are for numbers
and text. Those error types are neither. Errors such as those mentioned are
LOGICAL values. They are not numbers and they are not text.

Biff

Custom Format N/A #DIV/O!
 

You can't do it without using conditional formatting.

OTOH, it's generally poor practice to have errors hidden in your
worksheet. It's nearly always better to trap the errors instead. For
instance instead of

=A1/B1

where B1 could be blank or 0, use

=IF(B1<0, A1/B1,"")

or for #N/As, instead of

=VLOOKUP(A1,J:K,2,FALSE)

where A1 may not be found in J:J, use

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2, FALSE))

That way real errors (i.e., that you don't "expect") don't get lost
because your user is used to seeing the errors elsewhere.

In article ,
vane0326
wrote:

I would like to know if there is a custom format that will hide formula
errors. I'm not talking about conditional formatting.

Example:

This will hide Zero's

0;-0;;@

This will hide the The Bignum (E+307)

[=9.99999999999999E+307]"";General



But what will hide

#N/A and #DIV/O!

What do you think*?*

Custom Format N/A #DIV/O!
 

Yes that true JE McGimpsey what you say but when you have use

=IF(ISNA(MATCH(A1,J:J,FALSE)),"",VLOOKUP(A1,J:K,2, FALSE))


in a couple thousands rows the calculations takes a long time. Oh well
I thought there is a custom format for that.


Thanks guys.


--
vane0326
------------------------------------------------------------------------
vane0326's Profile: http://www.excelforum.com/member.php...o&userid=14731
View this thread: http://www.excelforum.com/showthread...hreadid=562310