ERROR.TYPEs need help!

[Sazzle scott]

I am trying to write an If statement that check if a cell has an ERROR.TYPE=7 it will appear as " " if false it will return the value of the cell. However it does not recognise the FALSE statement to return the value;

IF(ERROR.TYPE(B8)=7,"",B8)
I will have #N/A! 's in the main worksheet
I could create a another table to filter it out but I thought there has to be an easier way.

 

[Todd Bardoni]

What is error type 7???

Anyway, for N/A
=IF(ISNA(...)),"",...)

 

[just_jon]

I am trying to write an If statement that check if a cell has an ERROR.TYPE=7 it will appear as " " if false it will return the value of the cell. However it does not recognise the FALSE statement to return the value;

IF(ERROR.TYPE(B8)=7,"",B8)
I will have #N/A! 's in the main worksheet
I could create a another table to filter it out but I thought there has to be an easier way.

What do you get when TRUE, and what do you get when FALSE?

 

[Sazzle scott]

HI
ERROR.TYPE 7 is #N/A!

For True i get what I ask for which is " " ( a blank cell)

for False I get an Error message it doesn't want to retuen the value of the cell.

The cells the statement is referring to will either have #N/A! in them or a value i.e. 5
I want to return a blank cell if it sees #N/A! or if it doesn't then return the value of 5

i hope this makes sense

 

[Todd Bardoni]

FYI,
" " is not a blank cell...that is if you have a space in there like you posted.

"" will give you a blank cell.

Oh, and

=IF(ISNA(B8),"",B8)

or, if your error has a ! in it then how about,

=IF(B8="#N/A!","",B8)

 

[just_jon]

Seems, according to the cocumentation, that the ERROR.TYPE can be used only with error-filled cells. Use the construct tbardoni's given you, which does exactly what you want.

 

[Sazzle scott]

Thanks your an Angel!

Worked a treat!


Sazzle ray: