ERROR.TYPEs need help!

Sazzle scott

New Member
Joined
Feb 1, 2004
Messages
10
:rolleyes: 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. :oops:
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
Sazzle scott said:
:rolleyes: 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. :oops:

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

Sazzle scott

New Member
Joined
Feb 1, 2004
Messages
10
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
 
Upvote 0

Todd Bardoni

Well-known Member
Joined
Aug 29, 2002
Messages
3,042
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)
 
Upvote 0

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 
Upvote 0

Forum statistics

Threads
1,186,371
Messages
5,957,469
Members
438,307
Latest member
bigmike1720

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top