none
#N/A Error formula

    Question

  • I seem to be having a general issue with excel and various workbooks.  If I delete a row, then is spreads a #N/A error message throughout the worksheet.  There are no links to this file; auto calculate is on and I have gone through the other posts - and cannot seem to find how this is corrected.  

    The file was saved as excel 97-2003 but am working in excel 2010.  When I did a save as - and saved the workbook as an excel 2010 - then the #N/A was all over the workbook as well.

    I not super techy - but have been an excel user for many, many years - this is most frustrating!  Any help would be greatly appreciated.

    Wednesday, December 12, 2012 8:18 PM

Answers

  • Hello:

    From your post, there isn't quite enough to exactly determine your issue.  However, an #N/A error usually means that data is missing in a cell which is referenced by a formula and that missing data cell uses the NA function to signal missing data.  For example, if you deleted one line, it may have contained a data cell that was referenced by the formula.  You can use the IFERROR function to work around the issue if you want, but probably the best way is to determine why deleting one line would cause the problem.

    Another common cause is a VLOOKUP that can't find data.  If that's the issue, the workaround is fairly easy by modifying your VLOOKUP formula.  A sample of how to eliminate the #N/A in a VLOOKUP and replace it with "Not Found" would be as follows (for Excel 2007 or higher):

    =IFERROR(VLOOKUP(C20,VendorList,2,FALSE),"Not Found")

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com




    Wednesday, December 12, 2012 10:42 PM
  • Hello JSmugs:

    I personally have not had any issues with moving up to newer versions, but there have been a few posts.  I would suggest a couple of alternatives:

    (1) Upload your workbook to a SkyDrive account so someone in the forum could look at your issue
    (2) Consider the possibility that the original workbook was corrupt (yes, it happens occasionally)
    (3) Make sure there were no VBA User Defined functions.  They would disappear if you saved the file as an xlsx.
    (4) Start again back at your Excel 2003 format.
    (5) Open it in Excel 2010.
    (6) Save it as a the usual xls 2003 format... don't convert it at this point
    (7) Re-open it in Excel 2010 and then do your Save As xlsm (macro enabled workbook)
    (8) Close it and re-open the xlsm format.
    (9) See if the issue disappears

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    Wednesday, December 12, 2012 11:36 PM

All replies

  • Hello:

    From your post, there isn't quite enough to exactly determine your issue.  However, an #N/A error usually means that data is missing in a cell which is referenced by a formula and that missing data cell uses the NA function to signal missing data.  For example, if you deleted one line, it may have contained a data cell that was referenced by the formula.  You can use the IFERROR function to work around the issue if you want, but probably the best way is to determine why deleting one line would cause the problem.

    Another common cause is a VLOOKUP that can't find data.  If that's the issue, the workaround is fairly easy by modifying your VLOOKUP formula.  A sample of how to eliminate the #N/A in a VLOOKUP and replace it with "Not Found" would be as follows (for Excel 2007 or higher):

    =IFERROR(VLOOKUP(C20,VendorList,2,FALSE),"Not Found")

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com




    Wednesday, December 12, 2012 10:42 PM
  • Rich - thank you for your response!  There was actually no data, number or formula in the row being deleted - that is what is so frustrating.  Then on top of that - I use the EOMONTH formula to calculate months (I am a finance geek).  The formula for that is date input in column B (10/31/11); formula in column C is =EOMONTH(B1+1) which usually gives me then 11/30/11.  The row removed had nothing to do with this formula - yet the #N/A happened there too and then of course spread across every column ....

    I could see if a column had been deleted ... but this was a row.  I rarely use vlookup and this particular spreadsheet did not have any.  I will look into the IFERROR function.

    Thanks again.

    Wednesday, December 12, 2012 10:56 PM
  • Hello again :)

    You might check to make sure that the references weren't fixed references, such as $A$1 instead of A1.

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Wednesday, December 12, 2012 11:00 PM
  • Yes, did so and not a fixed reference.  Plus the row deleted was say down in 300+ range.  Do you know of any issues between excel 2010 and 97-2003?   Grasping at straws and this is not my only excel "issue".  This forum group stuff is new to me - as I am trying to avoid using Microsoft's fee based technical support.

    Thanks!

    Wednesday, December 12, 2012 11:06 PM
  • Hello JSmugs:

    I personally have not had any issues with moving up to newer versions, but there have been a few posts.  I would suggest a couple of alternatives:

    (1) Upload your workbook to a SkyDrive account so someone in the forum could look at your issue
    (2) Consider the possibility that the original workbook was corrupt (yes, it happens occasionally)
    (3) Make sure there were no VBA User Defined functions.  They would disappear if you saved the file as an xlsx.
    (4) Start again back at your Excel 2003 format.
    (5) Open it in Excel 2010.
    (6) Save it as a the usual xls 2003 format... don't convert it at this point
    (7) Re-open it in Excel 2010 and then do your Save As xlsm (macro enabled workbook)
    (8) Close it and re-open the xlsm format.
    (9) See if the issue disappears

    Regards,


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com


    Wednesday, December 12, 2012 11:36 PM