| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hello,
I have a formula which is working correctly; however when it doesn't locate the data (as will happens sometimes), it displays the #N/A error. I would prefer it display a blank cell. Can someone please tell me how to do this in Excel? Formula: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) Thanks, Eva Marie |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
=IF(ISNA(YourFormula),"",YourFormula)
-- Kind regards, Niek Otten Microsoft MVP - Excel "Eva Marie" <Eva wrote in message ... | Hello, | | I have a formula which is working correctly; however when it doesn't locate | the data (as will happens sometimes), it displays the #N/A error. I would | prefer it display a blank cell. Can someone please tell me how to do this in | Excel? | | Formula: =IF(P28=0,"",INDEX('Ext''d | Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d | Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) | | Thanks, | Eva Marie |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hi,
Thank you for your help. I just tried this and the answer is producing "FALSE" when there is something to be returned in the cell. For example: The formula: =IF(P27=0,"",IF(ISNA(INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P27,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A27,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),"")) In this case, I am asking it to return a date via the MATCH function (which works fine without the ISNA function). When I put in the ISNA (as shown above) and the answer should have been say, "10/13/08", it displays "FALSE" instead. Eva Marie "Niek Otten" wrote: =IF(ISNA(YourFormula),"",YourFormula) -- Kind regards, Niek Otten Microsoft MVP - Excel "Eva Marie" <Eva wrote in message ... | Hello, | | I have a formula which is working correctly; however when it doesn't locate | the data (as will happens sometimes), it displays the #N/A error. I would | prefer it display a blank cell. Can someone please tell me how to do this in | Excel? | | Formula: =IF(P28=0,"",INDEX('Ext''d | Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d | Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) | | Thanks, | Eva Marie |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula)
Indicatively for your expression, which uses 2 MATCHes: IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,M ATCH(1),MATCH(2))..) Try (all in the same cell): = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"", INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: I have a formula which is working correctly; however when it doesn't locate the data (as will happens sometimes), it displays the #N/A error. I would prefer it display a blank cell. Can someone please tell me how to do this in Excel? Formula: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) Thanks, Eva Marie |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Essentially, if there is nothing to return, I need the #N/A hidden and if
there is something to be returned (such as a date), I need that to be displayed, instead of the FALSE error. Thanks for your help! Eva Marie "Eva Marie" wrote: Hi, Thank you for your help. I just tried this and the answer is producing "FALSE" when there is something to be returned in the cell. For example: The formula: =IF(P27=0,"",IF(ISNA(INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P27,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A27,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),"")) In this case, I am asking it to return a date via the MATCH function (which works fine without the ISNA function). When I put in the ISNA (as shown above) and the answer should have been say, "10/13/08", it displays "FALSE" instead. Eva Marie "Niek Otten" wrote: =IF(ISNA(YourFormula),"",YourFormula) -- Kind regards, Niek Otten Microsoft MVP - Excel "Eva Marie" <Eva wrote in message ... | Hello, | | I have a formula which is working correctly; however when it doesn't locate | the data (as will happens sometimes), it displays the #N/A error. I would | prefer it display a blank cell. Can someone please tell me how to do this in | Excel? | | Formula: =IF(P28=0,"",INDEX('Ext''d | Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d | Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) | | Thanks, | Eva Marie |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hi Max,
Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie "Max" wrote: The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula) Indicatively for your expression, which uses 2 MATCHes: IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,M ATCH(1),MATCH(2))..) Try (all in the same cell): = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"", INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: I have a formula which is working correctly; however when it doesn't locate the data (as will happens sometimes), it displays the #N/A error. I would prefer it display a blank cell. Can someone please tell me how to do this in Excel? Formula: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) Thanks, Eva Marie |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Tough to check. Could be a data consistency issue, eg what should have
apparently matched (and returned a valid result) doesn't, because say, some dates are not real dates (real dates are numbers, and it won't match with text numbers), etc Can you upload a sample file using a free filehost, then post a link to it here? For example, you could use this free filehost to upload: http://www.freefilehosting.net/ Copy the "direct link" which is generated after you upload, then paste it here (Desensitize your sample as appropriate) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: Hi Max, Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hi,
Please show us your formula. We are working in the dark here. There is nothing wrong with any of the formulas suggested by Niek or Max. The problem is they don't know your formula and data. -- Thanks, Shane Devenshire "Eva Marie" wrote: Hi Max, Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie "Max" wrote: The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula) Indicatively for your expression, which uses 2 MATCHes: IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,M ATCH(1),MATCH(2))..) Try (all in the same cell): = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"", INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: I have a formula which is working correctly; however when it doesn't locate the data (as will happens sometimes), it displays the #N/A error. I would prefer it display a blank cell. Can someone please tell me how to do this in Excel? Formula: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) Thanks, Eva Marie |
|
#9
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hi Shane,
I'm sorry, but I don't understand your question. The formula is given below. The ooriginal formula was: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) With the original formula, The MATCH and INDEX are looking up dates in another tab and returning either a date (e.g., 10/13/08), or returning #N/A (and displaying the #N/A). The revised formula is the one provided by Max: = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") This formula is still looking up the date and if there isn't one, it hides the #N/A now, but if there IS a date, it ALSO hides the date. Can you please tell me what else I need to provide? Thank you, Eva Marie "ShaneDevenshire" wrote: Hi, Please show us your formula. We are working in the dark here. There is nothing wrong with any of the formulas suggested by Niek or Max. The problem is they don't know your formula and data. -- Thanks, Shane Devenshire "Eva Marie" wrote: Hi Max, Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie "Max" wrote: The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula) Indicatively for your expression, which uses 2 MATCHes: IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,M ATCH(1),MATCH(2))..) Try (all in the same cell): = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"", INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: I have a formula which is working correctly; however when it doesn't locate the data (as will happens sometimes), it displays the #N/A error. I would prefer it display a blank cell. Can someone please tell me how to do this in Excel? Formula: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) Thanks, Eva Marie |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hi Max,
I'm not sure if I will be able to do so as I work for a company that has secret military clearance and the data in the spreadsheet is classified. I will see what I can do to upload an example and post the link. Thanks! Eva Marie "Max" wrote: Tough to check. Could be a data consistency issue, eg what should have apparently matched (and returned a valid result) doesn't, because say, some dates are not real dates (real dates are numbers, and it won't match with text numbers), etc Can you upload a sample file using a free filehost, then post a link to it here? For example, you could use this free filehost to upload: http://www.freefilehosting.net/ Copy the "direct link" which is generated after you upload, then paste it here (Desensitize your sample as appropriate) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: Hi Max, Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
=IF(P28=0,"",IF(iserror(INDEX('Ext''d
Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))),"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1))))) "Eva Marie" wrote: Hi Shane, I'm sorry, but I don't understand your question. The formula is given below. The ooriginal formula was: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) With the original formula, The MATCH and INDEX are looking up dates in another tab and returning either a date (e.g., 10/13/08), or returning #N/A (and displaying the #N/A). The revised formula is the one provided by Max: = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") This formula is still looking up the date and if there isn't one, it hides the #N/A now, but if there IS a date, it ALSO hides the date. Can you please tell me what else I need to provide? Thank you, Eva Marie "ShaneDevenshire" wrote: Hi, Please show us your formula. We are working in the dark here. There is nothing wrong with any of the formulas suggested by Niek or Max. The problem is they don't know your formula and data. -- Thanks, Shane Devenshire "Eva Marie" wrote: Hi Max, Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie "Max" wrote: The key is to use ISNA like this: =IF(ISNA(MATCH(...)),"",<formula) Indicatively for your expression, which uses 2 MATCHes: IF(OR(ISNA(MATCH(1)),ISNA(MATCH(2))),"",INDEX(..,M ATCH(1),MATCH(2))..) Try (all in the same cell): = IF(OR(P28=0,A28=0), IF(ISNA(MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0)), IF(ISNA(MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)),"", INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)),""),"") -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: I have a formula which is working correctly; however when it doesn't locate the data (as will happens sometimes), it displays the #N/A error. I would prefer it display a blank cell. Can someone please tell me how to do this in Excel? Formula: =IF(P28=0,"",INDEX('Ext''d Lines'!$B$1:$CD$1,MATCH(P28,INDEX('Ext''d Lines'!$B$2:$CD$230,MATCH($A28,'Ext''d Lines'!$A$2:$A$230,0),0),1)+1)) Thanks, Eva Marie |
|
#12
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Try this
Select the column with the suspicious dates. Data - Text To Columns - Select Delimited - Select Next -Select Next Choose DATE and select your date format -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. Eva Marie" wrote: Hi Max, I'm not sure if I will be able to do so as I work for a company that has secret military clearance and the data in the spreadsheet is classified. I will see what I can do to upload an example and post the link. Thanks! Eva Marie "Max" wrote: Tough to check. Could be a data consistency issue, eg what should have apparently matched (and returned a valid result) doesn't, because say, some dates are not real dates (real dates are numbers, and it won't match with text numbers), etc Can you upload a sample file using a free filehost, then post a link to it here? For example, you could use this free filehost to upload: http://www.freefilehosting.net/ Copy the "direct link" which is generated after you upload, then paste it here (Desensitize your sample as appropriate) -- Max Singapore http://savefile.com/projects/236895 Downloads:18,300 Files:361 Subscribers:58 xdemechanik --- "Eva Marie" wrote: Hi Max, Thank you for your help too! I copied this entire formula into the cell and then dragged it down into the below cells to copy the formula into them. Unfortunately, when there is a value to be displayed, such as "10/13/08", it makes the cell blank, rather than displaying 10/13/08. It definitely works with the #N/A cells, where no value was returned. I'd appreciate any help on getting values to display, while still hiding the #N/A. Thanks! Eva Marie |
|
#13
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Hiding #N/A error (or displaying a blank cell instead of #N/A)
Hi,
An alternative to all this, if you just want to hide the #N/A is to use conditional formatting to change the text colour to match your background colour if the cell contains #N/A. Dave url:http://www.ureader.com/msg/10356562.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| AUtomatically hiding a row when a certain cell is blank | Excel Discussion (Misc queries) | |||
| Hiding the results of a formula generated from a blank cell | Excel Discussion (Misc queries) | |||
| cell text not displaying into next blank cell | Excel Discussion (Misc queries) | |||
| Hiding Rows if Blank cell | Excel Worksheet Functions | |||
| Displaying a blank cell | Excel Discussion (Misc queries) | |||
Linear Mode
