Sorry, Another Question about N/A's

jeh

Active Member
Joined
Apr 27, 2002
Messages
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,

:wink: Jim
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Hi Jim...
Am getting lazy, but really just too tired to look it up myself...

Search for "IsError" in Excel help.

Tom
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top