Hiding #N/A error (or displaying a blank cell instead of #N/A)

[Eva Marie]

Hello,

I have a formula which is working correctly; however when it doesn't locate
the data (as will happens sometimes), it displays the #N/A error. I would
prefer it display a blank cell. Can someone please tell me how to do this in
Excel?

Formula: =IF(P28=0,"",INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))

Thanks,
Eva Marie

 

[Niek Otten]

=IF(ISNA(YourFormula),"",YourFormula)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hello,
|
| I have a formula which is working correctly; however when it doesn't locate
| the data (as will happens sometimes), it displays the #N/A error. I would
| prefer it display a blank cell. Can someone please tell me how to do this in
| Excel?
|
| Formula: =IF(P28=0,"",INDEX('Ext''d
| Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
| Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))
|
| Thanks,
| Eva Marie

 

[Eva Marie]

Hi,

Thank you for your help. I just tried this and the answer is producing
"FALSE" when there is something to be returned in the cell. For example:

The formula: =IF(P27=0,"",IF(ISNA(INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P27,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A27,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""))

In this case, I am asking it to return a date via the MATCH function (which
works fine without the ISNA function). When I put in the ISNA (as shown
above) and the answer should have been say, "10/13/08", it displays "FALSE"
instead.

Eva Marie

 

[Max]

The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula>)
Indicatively for your expression, which uses 2 MATCHes:
IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,MATCH(1),MATCH(2))..)

Try (all in the same cell):
=
IF(OR(P28=0,A28=0),
IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)),
IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d
Lines'!$A$2:$A$230,0),0),1)),"",
INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"")
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik

 

[Eva Marie]

Essentially, if there is nothing to return, I need the #N/A hidden and if
there is something to be returned (such as a date), I need that to be
displayed, instead of the FALSE error.

Thanks for your help!
Eva Marie

 

[Eva Marie]

Hi Max,

Thank you for your help too! I copied this entire formula into the cell and
then dragged it down into the below cells to copy the formula into them.
Unfortunately, when there is a value to be displayed, such as "10/13/08", it
makes the cell blank, rather than displaying 10/13/08. It definitely works
with the #N/A cells, where no value was returned.

I'd appreciate any help on getting values to display, while still hiding the
#N/A.

Thanks!
Eva Marie

 

[Max]

Tough to check. Could be a data consistency issue, eg what should have
apparently matched (and returned a valid result) doesn't, because say, some
dates are not real dates (real dates are numbers, and it won't match with
text numbers), etc

Can you upload a sample file using a free filehost,
then post a link to it here?

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,300 Files:361 Subscribers:58
xdemechanik

 

[ShaneDevenshire]

Hi,

Please show us your formula. We are working in the dark here.

There is nothing wrong with any of the formulas suggested by Niek or Max.
The problem is they don't know your formula and data.

 

[Eva Marie]

Hi Shane,

I'm sorry, but I don't understand your question. The formula is given
below. The ooriginal formula was:

=IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))

With the original formula, The MATCH and INDEX are looking up dates in
another tab and returning either a date (e.g., 10/13/08), or returning #N/A
(and displaying the #N/A).

The revised formula is the one provided by Max:

= IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)),
IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d
Lines'!$A$2:$A$230,0),0),1)),"",INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"")

This formula is still looking up the date and if there isn't one, it hides
the #N/A now, but if there IS a date, it ALSO hides the date.

Can you please tell me what else I need to provide?

Thank you,
Eva Marie

 

[Eva Marie]

Hi Max,

I'm not sure if I will be able to do so as I work for a company that has
secret military clearance and the data in the spreadsheet is classified. I
will see what I can do to upload an example and post the link.

Thanks!
Eva Marie

 

[Sean Timmons]

=IF(P28=0,"",IF(iserror(INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d
Lines'!$A$2:$A$230,0),0),1)+1))),"",INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d
Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)))))

 

[Barb Reinhardt]

Try this

Select the column with the suspicious dates.
Data -> Text To Columns -> Select Delimited -> Select Next -Select Next
Choose DATE and select your date format

 

[Dave Curtis]

Hi,

An alternative to all this, if you just want to hide the #N/A is to use
conditional formatting to change the text colour to match your background
colour if the cell contains #N/A.

Dave

url:http://www.ureader.com/msg/10356562.aspx