Thanks:  0
Likes:  0

1. 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?

Jim

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

Search for "IsError" in Excel help.

Tom

3. 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. 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 ]

## User Tag List

#### Posting Permissions

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