Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I get rid of all the #N/A that are in my spreadsheet?
Is there a way to make all the #N/As in a spreadsheet disappear?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I get rid of all the #N/A that are in my spreadsheet?
I normally get the #NA Error from the VLookup Function. Use the "IF"
and "ISNA" functions to replace the error with some usable text. The formula below will search Column A and return the value in the second column if it finds a match for the value in cell C1 or the text "Not Found" if there is no match. =IF(ISNA(VLOOKUP(C2,$A$2:$B$6,2,FALSE)),"Not Found", VLOOKUP(C2,$A$2:$B$6,2,FALSE)) The logical test of the IF statement is looking for the #NA error from the VLOOKUP formula. If if returns the #NA error, the IF formula is true and the true part is processed. If the error is not found, the false part of the IF statement is processed, which just happens to be the very same VLOOKUP formula. The easiest I have found to make this work is to get the VLOOKUP function working first then add the IF and ISNA formulas. That way I know I do not have an issue the VLOOKUP formula. amirstal wrote: Is there a way to make all the #N/As in a spreadsheet disappear? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I get rid of all the #N/A that are in my spreadsheet?
This is the actual formula I have:
=if(ac23="a",u23*vlookup(h23,rates!$a$1:$d$14,4),i f(ac23="b",u23/vlookup(h23,rates!$a$1:$d$14,4),if(ac23="c",u23/vlookup(h23,rates!$a$1:$d$14,4)))) But I could not insert the ISNA in so it will work. Can you help? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I get rid of all the #N/A that are in my spreadsheet?
Add the ISNA trap to all cells with this macro.
Sub NATrapAdd() Dim myStr As String Dim cel As Range For Each cel In Selection If cel.HasFormula = True Then If Not cel.Formula Like "=IF(ISNA*" Then myStr = Right(cel.Formula, Len(cel.Formula) - 1) cel.Value = "=IF(ISNA(" & myStr & "),""""," & myStr & ")" End If End If Next End Sub Gord Dibben MS Excel MVP On 11 Dec 2006 06:46:34 -0800, "amirstal" wrote: This is the actual formula I have: =if(ac23="a",u23*vlookup(h23,rates!$a$1:$d$14,4), if(ac23="b",u23/vlookup(h23,rates!$a$1:$d$14,4),if(ac23="c",u23/vlookup(h23,rates!$a$1:$d$14,4)))) But I could not insert the ISNA in so it will work. Can you help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
link a local spreadsheet to a network shared spreadsheet | Setting up and Configuration of Excel | |||
Transmitting data from a server spreadsheet to a client spreadsheet | Excel Programming | |||
conversion of MS Works Spreadsheet to Excel 2002 Spreadsheet | Excel Discussion (Misc queries) | |||
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? | Excel Discussion (Misc queries) | |||
How to open another Excel spreadsheet to copy data into current spreadsheet ? | Excel Programming |