+ Reply to Thread
Results 1 to 9 of 9

Formulas Suddenly Stop Calculating and Return #Value error

  1. 07-13-2013, 03:41 PM #1
    KC8SPO
    KC8SPO is offline
    Registered User
    Join Date
    07-13-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Formulas Suddenly Stop Calculating and Return #Value error

    I am using an INDEX/MATCH combination to return a value based on multiple criteria. Initially I was using an IF statement inside the MATCH function, but now I have moved to the concatenate method for evaluating 3 criteria. It was working fine before, however, now the formula only returns #VALUE or displays the text of the formula itself. What is interesting is that the formula works as long as I do not click in the formula bar. Once I do that, it will only show #VALUE as the result.

    The workbook is set to calculate automatically and I have tried both manual and auto with no luck.

    Sadly, even when I enter the data from Microsoft's example (http://support.microsoft.com/kb/59482), I still get the same error so I am getting very frustrated. Any help would be immensely appreciated!!
    Reply With QuoteRegister To Reply
  2. 07-13-2013, 03:45 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    Hi and welcome to the forum

    Can you upload the offending file? or a sample if it contains sensitive data

    If the format set to text? try setting to number, the F2 and enter
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 07-13-2013, 03:47 PM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    It sounds like the formula is an array formula but you may not be entering it as an array formula.

    Post the formula so we can see what it looks like.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 07-13-2013, 04:05 PM #4
    KC8SPO
    KC8SPO is offline
    Registered User
    Join Date
    07-13-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    I can upload the original file (see attached sheet, "Summary" tab, cell H2). The sheet was working just fine for the last week and then just late last night something changed. Any new formula causes the formula to stop working. I did notice that the format was somehow set to text, so once I changed it to general, it does not show the formula as text any longer, however the #VALUE issue is still giving me headaches.

    The tab "Sheet 2" has the example from the Microsoft help site and I still cannot get it to work.

    LTQ Planning.xlsx
    Reply With QuoteRegister To Reply
  5. 07-13-2013, 05:04 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    In your MODE column (Papa's), you only have NORMAL, but you are searching for ELITE

    Also, try this slightly shorter array formula...
    =INDEX('Papa''s New Bag LTQ'!M$2:M$1000, MATCH($B2&$C2,'Papa''s New Bag LTQ'!$B$2:$B$1000&'Papa''s New Bag LTQ'!$C$2:$C$1000,0),1)
    instead of
    =INDEX('Papa''s New Bag LTQ'!M$2:M$1000, MATCH(A2&B2&C2, 'Papa''s New Bag LTQ'!A2:A1000&'Papa''s New Bag LTQ'!B2:B1000&'Papa''s New Bag LTQ'!C2:C1000, 0))

    ...ARRAY formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Last edited by FDibbins; 07-13-2013 at 05:07 PM.
    Reply With QuoteRegister To Reply
  6. 07-13-2013, 05:13 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Not necessarily.

    You can normally enter an array formula and still get the correct result.

    I made a suggestion that that sentence be corrected but the powers that be said NO.
    Reply With QuoteRegister To Reply
  7. 07-13-2013, 05:15 PM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    Thanks Tony. Its a canned reply (as you probably already knew lol)
    Reply With QuoteRegister To Reply
  8. 07-13-2013, 05:19 PM #8
    KC8SPO
    KC8SPO is offline
    Registered User
    Join Date
    07-13-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    Thanks FD, that did the trick!! I have not worked with array formulas much and that must be what I was neglecting to do.

    I can stop banging my head against the table now!
    Reply With QuoteRegister To Reply
  9. 07-13-2013, 05:33 PM #9
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,936

    Re: Formulas Suddenly Stop Calculating and Return #Value error

    Happy to help and thanks for the feedback
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] If cell is empty in a variable range, stop macro and return error message
    By Andrei Kononenko in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-22-2019, 02:19 PM
  2. Index formulas suddenly don't work
    By FortuneSyn in forum Excel General
    Replies: 5
    Last Post: 05-03-2010, 04:50 PM
  3. Formulas stop calculating
    By brodiemac in forum Excel General
    Replies: 2
    Last Post: 06-27-2005, 01:05 PM
  4. Icons at the top are suddenly large. How do I return them to norm
    By Carly in forum Excel General
    Replies: 2
    Last Post: 05-13-2005, 03:06 PM
  5. [SOLVED] What causes my number pad on my keyboard to suddenly stop functio.
    By Tennessee in forum Excel General
    Replies: 1
    Last Post: 03-24-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1