How do I hide #N/A?

[MelbTim]

Hi,

I have a large spreadsheet with a lot of formulas. A lot of cells end
up with the value #N/A. Is there a way to hide that value #N/A (ie so
that it looks like there is nothing in the cells)? I hope I'm not
displaying my ignorance here. I have a feeling it's possible and
probably quite simple.

Thanks

Tim

 

[MelbTim]

Each cell has a VLOOKUP function in it. It automatically takes data
from another spreadsheet and when it runs out of data it starts
returning #N/A. It's really for neatness of presentation that I want
to eliminate it as I typically end up with most of the screen filled
with #N/A.

Thanks for any further suggestions you can offer.

Tim

 

[Guest]

Hi,

I have a large spreadsheet with a lot of formulas. A lot of cells end
up with the value #N/A. Is there a way to hide that value #N/A (ie so
that it looks like there is nothing in the cells)? I hope I'm not
displaying my ignorance here. I have a feeling it's possible and
probably quite simple.

Thanks

Tim

Some people just replace the N/A with 0 or blank as Dominic describes.
Personally I don't like that since it can be misleading in some cases,
but a lot of people do like it.

My solution is generally to just use conditional formatting so that if
#N/A is displayed in a cell the text is formatted as light grey and
perhaps a smaller font than the rest of the page. I can still see that
the cells are N/A, but they're not so distracting as they are in normal
font.

Good luck....

Bill

 

[dominicb]

Hi MelbTim

Can you give me an example of one of the formulae that returns an error
- I need to see how many parameters you're using.

DominicB

 

[MelbTim]

Thanks again for your help.

This is what is in cell B12 (other cells have only marginally different
formulae):

=VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0)

Cell A12 contains the number 12. The first column of the other
spreadsheet (which holds all the source data) doesn't contain the value
12 which generates the #N/A response.

I hope this helps.

Thanks again.

Tim

 

[mangesh_yadav]

Hi MelbTim,

When you use VLOOKUP and the search item is not present in the table,
it returns #N/A. To avoid this, you could use some thing like:
=IF(ISERR(VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0)),VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0),"")

This will put a blank space in the cell where you now get the #N/A

Mangesh

 

[Bobbie]

OK, so I'll assume that you know why the errors are there, and it is OK
with you for them to be there. If that is the case, and you are just
looking to hide them, a little trick I use is the change the color of
the font for those cells to white. You will still be able to see the
contents of the cell in the function bar when you select one of the
cells with the error in it. But you won't see the errors when you've
selected any other cell on the worksheet or when you print the
worksheet...assuming you use regular, white paper. I use this little
trick when I want top have a side calculation just for my own purposes.

But, as Dominicb said above, it is best to understand what is actually
causing the error and determine how to correct it appropriately.

Maybe this will help..
bb

 

[Peo Sjoblom]

=IF(ISNUMBER(MATCH($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$A$10000,0)),VLOOKUP($A12,'[referraldata.xls]autodata(Bei)'!$A$6:$I$10000,2,0),"")


--
Regards,

Peo Sjoblom

(No private emails please)

 

[MelbTim]

Thank you all so much for your help. It's very much appreciated.

Cheers

Tim

 

[mpiccione]

Just use the "*ISNA*" formula.
It returns TRUE if the value asked is #N/A. So in that case, just ask
if it is #N/A and replace the true response for whatever you want.


Hope it helps!

regards

Matías