replace N/A by a blank

How do i replace a #N/A  that I get sometimes in a Vlookup by A  blank ? or 
the value if eveything is fine

TKS
0
yyy1 (1)
6/15/2005 4:35:02 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
457 Views

Similar Articles

[PageSpeed] 2

One way:

    
=IF(ISNA(MATCH(A1,Sheet2!A:A,FALSE)),"",VLOOKUP(A1,Sheet2!A:B,2,FALSE)


In article <C7212BB1-1BC7-4546-ADFD-1985ED9AE697@microsoft.com>,
 yyy <yyy@discussions.microsoft.com> wrote:

> How do i replace a #N/A  that I get sometimes in a Vlookup by A  blank ? or 
> the value if eveything is fine
> 
> TKS
0
jemcgimpsey (6723)
6/15/2005 4:47:38 PM
Reply:

Similar Artilces:

how do I set my tab to stop at column N and rtn to A
I have an invoice created in excel that I would like to be able to tab over no further than the end of the invoice(column N) and then return to column A. Howeer, the tab continues past the column and I don't know how to lock it. TL, You can hide all the columns past column N, and the tab will stop at column. If youve been tabbing, from column A for example, and have arrived in column N, the Enter key will return you to column A, one row down. Otherwise, you can use the Home key to get back to column A, then the Enter key do go down one row. If that isn't good enough, you can...

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

remove duplicates except blanks
is it possible to have a macro that removes duplicates except blanks in a column? jat It is not a problem, but we need more information. Which column do you want to compare, should both entries be deleted? Regards, Per "jat" <jat@discussions.microsoft.com> skrev i meddelelsen news:D6C4A7E1-B4AE-4FD6-8E44-A6C1FE212595@microsoft.com... > is it possible to have a macro that removes duplicates except blanks in a > column? > > jat > With data in ColA try the below macro which will use ColB as a helper column... Sub Macro1() Columns(1...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

Links into blank cells
I have a formula which sums 2 or more cells in a separate worksheet. However if any of these cells are blank the formula returns a '#VALUE'. Bizarrely the same formula into a different worksheet does not cause this error message. Any idea's other than a long IFISERROR formula? The cells in the separate worksheets may contain a blank space, as if someone typed just the space bar and pressed return. That cell would look blank, visually, but Excel would evaluate it as a text entry and return the VALUE error because Excel doesn't perform math on a text string (without some ext...

top N values per group using VBA
Hello, I am trying to follow an example for finding top N values using VBA given at this link: http://support.microsoft.com/kb/210039/en-us#appliesto I've had success with this in the Northwind example database (with my own tables or with Northwind tables) ... But in my own databases (with my own tables) I get "Compile error: User'defined type not defined" with ", db As Database" highlighted in the code when I compile in the module and "Compile error.in query expression '(((Orders.OrderDate)>=NthInGroup([Customers].[CustomerID],5)))'."...

Go to Blank Cell Macro Problem
Hi I am trying to get a macro to go to the first blank cell in a specific column i.e. column A has data from row A1 to A15. I'd want to run a macro that will select cell A16. I found this code below which does this on a 'Normal' sheet. The problem i have is that the window is split and frozen at cell A1 (to retain headings and the Button for the macro). This seems to be preventing the code from running correctly. Can anyone suggest an alteration to the code, or a different approach? .....Sub findfirstempty() Set Rng = Range("a2") If Not IsEmpty(Rng.Value) Then If IsE...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Dbl Click Excel file and it opens to blank screen.
I have a customer that has started having this problem recently, when she dbl clicks an excel file, excel opens up and it doesn't open the file. She then has to click open and browse the file. All other office products seem to work fine. Has anybody seen this before. Try this: <Tools> <Options> <General> tab, And *uncheck* "IgnoreOtherApplications". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Tom Csanadi" <to...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...

Replacing old Windows XP and Outlook Express with new Windows 7
How do I export the contents and settings out of Outlook Express and import the accounts, settings and emails into Windows Live Mail on another machine? For messages: Copy the *ENTIRE* OE message store folder to flash or CD. (Folders.dbx must be included). Place this on the Desktop or other location on the machine using WLMail. Open WLMail and: File | Import | Messages | Microsoft Outlook Express 6 and point to where you saved it. For Addresses: Open the Address Book in OE and File | Export | Address Book (wab) and save it to the Desktop. Copy to flash or CD. Place this on the...

Do you want to replace the contents of
When my macro runs, it stops after this procdure and asks "Do you want to replace the contents ofthe destination cells" is there something I can add that will automatically tell it yes? Here is the part of the macro that it stops on. Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _ :="/", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)) Thanks H...

How do I replace just a few characters?
Hello, I have an excel worksheet with a street name column. I need to rename some names from "Main Street" to "Main St". I can do it for one name easily with find and replace, but how do I make it work for an entire column of different names such as "Easy Street" and "Main Street". If I do find "* Street" and replace with "* ST" I get "* ST" for the output instead of "Easy ST" What am I missing? -- Tomsriv ------------------------------------------------------------------------ Tomsriv's Profile: http:/...

Blank JPG file attachments
Why is it that at times when I send out an e-mail it will include several "image*.jpg" file attachments, which are blank. They're only 2kb, but they tend to be an annoyance among those I send e-mails to. I don't even have any pictures in the e-mails I sent. I may, though, copy text from a Word file into the e-mail before sending. Please let me know what I can do to cease these JPGs from attaching to outgoing e-mails. Thanks! ...

Still looking to be rid of the #N/A on look up table
I am not good at this at all, novice at best. This is my current formula, are you able to tell me how to alter my formula to yield a - or 0? I appreciate your help =VLOOKUP(F12:F45,Sheet3!G4:H51,2,FALSE) =IF(ISERROR(VLOOKUP(F12:F45,Sheet3!G4:H51,2,FALSE)),"-",VLOOKUP(F12:F45,Sheet3!G4:H51,2,FALSE)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "smiley61799" wrote: > I am not good at this at all, novice at best. This is my current formula, > are you able to tell me how to alter my formula to yield a - or 0? I >...

VBA to find filename and replace
Hi I just found the answer to "Find and Replace in VBA" and tried to adapt it for what I'm doing, but it's more difficult so I need some help please. I have multiple worksheets in an excel 2007 workbook - a monthly report. Within the worksheets I have many cells that link to last months workbooks - (i.e I compare last month figures to this months figures). I need the macro to do the following: - Create an Input box for the user to "enter the name ofTHE OLD months workbook" - Create an Input box for the user to "enter the name of THE NEW mont...

Printing cells that are blank, because there is a formula
I'm using Excel 03 and I have a column of cells that have a formula =if(e10="","",e10+90) Is there a way Excel will recognize these cells to print when there's an actual value but not when the value is "" Thanks Left to its own devices, excel will include those cells in the print range--they contain something (that formula), so those cells will be included. But you can do something to tell excel what you want... Saved from a previous post (so you'll have to adjust the sheet names and column letters and even the columns to print (A:X...

Replacing Character
I need to replace/delete the " >" character at the end of a number... when the data was brought into the excel 1234> was the number that cam it happens in about 7000 lines any suggestions on how to replace it with a blank?? thank -- tico31p ----------------------------------------------------------------------- tico31pl's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1040 View this thread: http://www.excelforum.com/showthread.php?threadid=46572 Select the range to fix. Edit|Replace what: " >" (without the quotes with: (leav...

Remove blank spaces in an address
Hi there, is there a way to delete a blank spaces from an address i.e. 124 High Street would then be 124HighStreet I've played around with LEFT and RIGHT functions and & " " but can't quite get it. Any help would be appreciated. Try SUBSTITUTE: =SUBSTITUTE(A1," ","") HTH Jason Atlanta, GA >-----Original Message----- >Hi there, > >is there a way to delete a blank spaces from an address > >i.e. 124 High Street > >would then be 124HighStreet > >I've played around with LEFT and RIGHT functions and & ...

Replacing A Column Of Numbers
Hello, New user of Excel 2007, and Excel in general. Should be simple, but I just can't figure out how. Have a column of numbers in Column A In Column B (row 1)I have the formula of what's in A (row 1) multiplied by 1000 And I have dragged this formula downwards, so I now have in each row B what is in the same row in Column A multiplied by 1000. So far, all is fine. Now, I want to replace what's in Column A by the newly generated values in Column B (row by row replacement) I can't just Cut out everything in Column A, as B then disappears, as, I'v...

Find / Replace
In a field, I would like to update all instances of "Daily Billing" to "Rental Payment - Daily Billing". The Replace function works the first time, but when I add data to the file, and run the query again, it updates the fields which have already been upated to "Rental Payment - Rental Payment - Daily Billing". I can see why it is doing this, but is there a way for the update query to look at just the first 11 characters of the field and only perform the update if it finds the "Daily Billing" text there? Thanks. Use "Daily Billing" as ...