| |
How do I hide #N/A, ### in cells not yet worked on? in Excel Worksheet Functions |
|
|
|
|
|
|
|
|
|
|
|
|
| |
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" <Sunny9210@discussions.microsoft.com> wrote in message
> news:118D8083-B8FB-46C5-B8E5-51AF80A92044@microsoft.com...
> > 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?
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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" <Sunny9210@discussions.microsoft.com> wrote in message
news:1E4BB9FE-20B0-4036-B035-7100A061B67F@microsoft.com...
> 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > news:118D8083-B8FB-46C5-B8E5-51AF80A92044@microsoft.com...
> > > 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?
> >
> >
> >
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Sunny9210@discussions.microsoft.com> wrote in message
> news:1E4BB9FE-20B0-4036-B035-7100A061B67F@microsoft.com...
> > 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > > news:118D8083-B8FB-46C5-B8E5-51AF80A92044@microsoft.com...
> > > > 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?
> > >
> > >
> > >
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Sunny9210@discussions.microsoft.com> wrote in message
news:4DB30494-441D-401C-842B-714414CD00C7@microsoft.com...
> 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > news:1E4BB9FE-20B0-4036-B035-7100A061B67F@microsoft.com...
> > > 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > > > news:118D8083-B8FB-46C5-B8E5-51AF80A92044@microsoft.com...
> > > > > 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?
> > > >
> > > >
> > > >
> >
> >
> >
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Sunny9210@discussions.microsoft.com> wrote in message
> news:4DB30494-441D-401C-842B-714414CD00C7@microsoft.com...
> > 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > > news:1E4BB9FE-20B0-4036-B035-7100A061B67F@microsoft.com...
> > > > 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > > > > news:118D8083-B8FB-46C5-B8E5-51AF80A92044@microsoft.com...
> > > > > > 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?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <Sunny9210@discussions.microsoft.com> wrote in message
> > news:4DB30494-441D-401C-842B-714414CD00C7@microsoft.com...
> > > 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > > > news:1E4BB9FE-20B0-4036-B035-7100A061B67F@microsoft.com...
> > > > > 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" <Sunny9210@discussions.microsoft.com> wrote in message
> > > > > > news:118D8083-B8FB-46C5-B8E5-51AF80A92044@microsoft.com...
> > > > > > > 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?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> > |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
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 <janaban...@discussions.microsoft.com>
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 -
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
 |
Question
Answer
Notify me of replies