Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Sorry, Another Question about N/A's

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Washington State, USA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I have a range of cells that are from a lookup function that I am pasting to another sheet. The problem is that where there is no data from the lookup it gives an N/A, and will paste the N/A's to the other sheet.

    I don't mind the N/A's in sheet from where I'm copying from, but it does matter to where I'm pasting it to, because this sheet will be printed. Is there a way to say that if the value of the cell is N/A, then show nothing at all?

    Thank you for your time,

    Jim

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jim...
    Am getting lazy, but really just too tired to look it up myself...

    Search for "IsError" in Excel help.

    Tom

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Washington State, USA
    Posts
    250
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom--I'm ashamed to say it, but I did look in help and couldn't find what I wanted, or couldn't understand the solution.



    If I could figure out how to put something in this formula that would say if a18 is blank then show nothing.
    =LOOKUP(A18,Sheet3!B2:D7736)

    Thanks,

    Jim

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    KC, MO
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you looking to modify your destination sheet so those cells don't print out or actually modify what Excel sits out (the N/A bit)?

    Assuming the former and not the latter,

    quick and dirty:

    conditional formatting
    (cell value is) (equal to) (="N/A")
    format text to white font in white background
    it'll still be there, but it won't show up if you print it...

    extended:
    write a macro on your sheet that loops through each cell that you have data in and executes something similar to the following:

    Sub NA_Remove()

    r = 1
    While (Range("C" & r).Value <> "")
    If Range("C" & r).Value = "N/A" Then
    'select row r
    'delete row r
    End If
    r = r + 1
    Wend

    End Sub



    _________________
    Regards,

    Rob Fritts

    [ This Message was edited by: robfritts on 2002-04-30 01:06 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •