Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
A spreadsheet I am working on return #n/a into a cell as it only dins a blank
in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
The #N/A error is usually the result of a formula..........try wrapping that formula in an IF statement, like...... =IF(ISNA(YourFormula),"",YourFormula) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: A spreadsheet I am working on return #n/a into a cell as it only dins a blank in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
Nope, doesn't like that. The formula in the cell is a lookup
=if(isna(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"" ,(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Doesn't want to close the formula. ??? "CLR" wrote: The #N/A error is usually the result of a formula..........try wrapping that formula in an IF statement, like...... =IF(ISNA(YourFormula),"",YourFormula) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: A spreadsheet I am working on return #n/a into a cell as it only dins a blank in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
Try it this way..........
=if(isna(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"", (LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: Nope, doesn't like that. The formula in the cell is a lookup =if(isna(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"" ,(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Doesn't want to close the formula. ??? "CLR" wrote: The #N/A error is usually the result of a formula..........try wrapping that formula in an IF statement, like...... =IF(ISNA(YourFormula),"",YourFormula) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: A spreadsheet I am working on return #n/a into a cell as it only dins a blank in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
Needs to be rewritten as
=IF(ISNA(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"", LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Note where pairs of parenthesis along with = symbols disappeared within the formula when written as an IF statement. "The Toasterman" wrote: Nope, doesn't like that. The formula in the cell is a lookup =if(isna(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"" ,(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Doesn't want to close the formula. ??? "CLR" wrote: The #N/A error is usually the result of a formula..........try wrapping that formula in an IF statement, like...... =IF(ISNA(YourFormula),"",YourFormula) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: A spreadsheet I am working on return #n/a into a cell as it only dins a blank in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
Thanks guys, works well.
Cheers "JLatham" wrote: Needs to be rewritten as =IF(ISNA(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"", LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Note where pairs of parenthesis along with = symbols disappeared within the formula when written as an IF statement. "The Toasterman" wrote: Nope, doesn't like that. The formula in the cell is a lookup =if(isna(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"" ,(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Doesn't want to close the formula. ??? "CLR" wrote: The #N/A error is usually the result of a formula..........try wrapping that formula in an IF statement, like...... =IF(ISNA(YourFormula),"",YourFormula) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: A spreadsheet I am working on return #n/a into a cell as it only dins a blank in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ignoring #n/a
If you have a great whack of these, maybe this macro will help.
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 Wed, 28 Jun 2006 08:20:01 -0700, The Toasterman wrote: Thanks guys, works well. Cheers "JLatham" wrote: Needs to be rewritten as =IF(ISNA(LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"", LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Note where pairs of parenthesis along with = symbols disappeared within the formula when written as an IF statement. "The Toasterman" wrote: Nope, doesn't like that. The formula in the cell is a lookup =if(isna(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)),"" ,(=LOOKUP($H13,$H$48:$I$98,$G$48:$G$98)) Doesn't want to close the formula. ??? "CLR" wrote: The #N/A error is usually the result of a formula..........try wrapping that formula in an IF statement, like...... =IF(ISNA(YourFormula),"",YourFormula) Vaya con Dios, Chuck, CABGx3 "The Toasterman" wrote: A spreadsheet I am working on return #n/a into a cell as it only dins a blank in the validation menu we are using. Is there any way to get excel to ignore #n/a & show a blank? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ignoring characters when sorting | Excel Discussion (Misc queries) | |||
30 Day Moving Average Ignoring Blank Cells | Excel Worksheet Functions | |||
alphabetical sort while ignoring preceeding numbers/symbols | Excel Discussion (Misc queries) | |||
Ignoring Initial Articles in Field Sort | Excel Discussion (Misc queries) | |||
Averaging noncontiguous numbers ignoring zeros? | Excel Worksheet Functions |