How do I hide #N/A, ### in cells not yet worked on?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My spreadsheet is a pricing sheet with formulae using lookup in another
worksheet so it fills in the rest of the row automatically. All rows below
what has been filled in show #N/A in one column and ### in 3 columns. How
can I hide this so the sheet looks clean and still maintain the formulae?
 
The #N/A can be hidden with a formula like

=IF(ISNA(formula),"",formula)

The #### might be because the columns are not wide enough, or may be as
result of the #N/A. Try both.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob, I know this is the right formula. My original formula is:
=LOOKUP(C13,hinge)
I have trouble trying to apply the formula below with the last part where it
says ",formula)" No matter how I type it in I get the message that there
are errors in my formula. The next 2 columns in my spreadsheet have the
formula
=LOOKUP(D13,doors) and =LOOKUP(D13,drawers) each of which would return a
number of 1 or 2 so it's not that the column isn't wide enough. Until I
enter the item code in B13, all the cells show #N/A, or ### because values
haven't been found.
 
As far as I know, you can't get an N/A with LOOKUP, VLOOKUP,MATCH, yes, but
not LOOKUP.

What is in D13, and what is in the hinge range?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
OK, my column headers are as follows:
Item No., Qty., Item Code, Hinge, Fin. Side, No. Doors, No. Drawers, Item
Description, Net Price Each, List Price Each, Total
We are a kitchen cabinet manufacturer and this is a pricing spreadsheet for
a quote. Item No., Qty. and Finished Side must be filled in manually. As
soon as an Item Code is filled in, (ex. W2430), it fills in automatically
across the row. The hinge column result will be Left or Right, Door & Drawer
result will be 1 or 2. The spreadsheet is working. It's just that until
Item Code is typed in, all rows below it show the #N/A in the hinge column
and ### in the Doors and Drawers column and #N/A in the Item Description
Column. I would like these not to show.
 
I am still at a loss to know where you get the #N/A from in LOOKUP. Can you
mail me the spreadsheet?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I tried to email it to you this morning but it bounced back. I see in
another post you said to drop the "nothere" so I will try that.
 
Hey there, was curious if you got your problem resolved? I kind of have the
same thing, tried everything and can't get rid of those #N/A.
 
Format text in cells that contain errors so that they don't show
Select the cells that contain the error value.
On the Format menu, click Conditional Formatting.
In the box on the left, click Formula Is.

In the box on the right, type =ISERROR(reference), where reference is a
reference to the cell that contains the error value.
Click Format, and then click the Font tab.

Click Format.
In the Color box, select white.
 
It is better to intercept errors or to stop them occurring, and you
can do this in a few ways, eg:

=IF(lookup_cell="","",your_formula)
or
=IF(ISNA(your_formula),"",your_formula)
or
=IF(cell_with_first_lookup="","",your_formula)

Hope this helps.

Pete
 
Working with the suggestions from Pete_uk, the following formuls worked for me.
=IF(ISNA(MATCH(value,array,0)),"",value)
 
The normal method of handling #N/A is to use:
=if(iserror(yourformula),"",yourformula)

Cells that display ### mean that the column is not wide enough to display
the results. Widen the column or reduce the font size used.

Regards,
Fred
 
Back
Top