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
------------------------------------------------------------------------
MelbTim's Profile: http://www.excelforum.com/member.php...o&userid=23847
View this thread: http://www.excelforum.com/showthread...hreadid=375130

Good morning MelbTim

The standard way of trapping errors using formulae is using the ISERR()
function (which doesn't work with the #N/A error) and the ISERROR()
function. In general if you have a formula A1/B1 that is causing the
error then this formula in it's place would return 0:

=IF(ISERROR(A1/B1),0,A1/B1)

Really, you need to include details of what's causing the error - for
example if it's a lookup formula, it may be easier to solve.

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=375130

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


--
MelbTim
------------------------------------------------------------------------
MelbTim's Profile: http://www.excelforum.com/member.php...o&userid=23847
View this thread: http://www.excelforum.com/showthread...hreadid=375130

MelbTim wrote:
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

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


--
dominicb
------------------------------------------------------------------------
dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932
View this thread: http://www.excelforum.com/showthread...hreadid=375130

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


--
MelbTim
------------------------------------------------------------------------
MelbTim's Profile: http://www.excelforum.com/member.php...o&userid=23847
View this thread: http://www.excelforum.com/showthread...hreadid=375130

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=375130

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

=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" wrote in
message ...

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


--
MelbTim
------------------------------------------------------------------------
MelbTim's Profile:
http://www.excelforum.com/member.php...o&userid=23847
View this thread: http://www.excelforum.com/showthread...hreadid=375130

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

Cheers

Tim


--
MelbTim
------------------------------------------------------------------------
MelbTim's Profile: http://www.excelforum.com/member.php...o&userid=23847
View this thread: http://www.excelforum.com/showthread...hreadid=375130

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 :)


--
mpiccione
------------------------------------------------------------------------
mpiccione's Profile: http://www.excelforum.com/member.php...o&userid=24575
View this thread: http://www.excelforum.com/showthread...hreadid=375130