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)


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

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.

"Bob Phillips" wrote:

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)


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

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)


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

"Bob Phillips" wrote:

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)


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

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.

"Bob Phillips" wrote:

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)


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

"Bob Phillips" wrote:

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)


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

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)


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

"Bob Phillips" wrote:

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)


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

"Bob Phillips" wrote:

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)


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

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.

"Bob Phillips" wrote:

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)


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

"Bob Phillips" wrote:

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)


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

"Bob Phillips" wrote:

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)


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

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.

"Sunny9210" wrote:

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.

"Bob Phillips" wrote:

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)


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

"Bob Phillips" wrote:

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)


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

"Bob Phillips" wrote:

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)


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

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.
--
jana


"Sunny9210" wrote:

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?

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

On Aug 14, 3:28*pm, janabanana
wrote:
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.
--
jana



"Sunny9210" wrote:
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?- Hide quoted text -

- Show quoted text -

Working with the suggestions from Pete_uk, the following formuls worked for me.
=IF(ISNA(MATCH(value,array,0)),"",value)
--
newbie


"Sunny9210" wrote:

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

"newbie" wrote in message
...
Working with the suggestions from Pete_uk, the following formuls worked
for me.
=IF(ISNA(MATCH(value,array,0)),"",value)
--
newbie


"Sunny9210" wrote:

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?