Sorry, Another Question about N/A's

jeh · Apr 30th, 2002, 01:27 AM

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

Tom Schreiner · Apr 30th, 2002, 01:42 AM

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

Search for "IsError" in Excel help.

Tom

jeh · Apr 30th, 2002, 01:51 AM

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

robfritts · Apr 30th, 2002, 01:57 AM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 30th, 2002, 01:27 AM	#1
jeh Board Regular Join Date: Apr 2002 Location: Washington State, USA Posts: 250	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

Apr 30th, 2002, 01:42 AM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Hi Jim... Am getting lazy, but really just too tired to look it up myself... Search for "IsError" in Excel help. Tom

Apr 30th, 2002, 01:51 AM	#3
jeh Board Regular Join Date: Apr 2002 Location: Washington State, USA Posts: 250	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

Apr 30th, 2002, 01:57 AM	#4
robfritts New Member Join Date: Apr 2002 Location: KC, MO Posts: 18	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 ]