+ Reply to Thread
Results 1 to 13 of 13

Array formula suddenly stopped working

  1. #1
    Registered User
    Join Date
    08-12-2005
    Posts
    7

    Array formula suddenly stopped working

    Hi,

    I have an array formula that was working totally fine yesterday, and today when I opened my workbook it gave me "#REF" value. Today is the day I'm supposed to hand over the spreadsheet to my client, so this is very frustrating and time sensitive.

    The formula is an array formula (dragged across several rows and columns). This is the formula:

    =INDEX($KV$6:INDIRECT("$KU$"&$KQ$2), SMALL(IF($KY$5=$KU$6:INDIRECT("$KT"&$KQ$2), ROW($KU$6:INDIRECT("$KT$"&$KQ$2))-ROW($KU$6)+1), ROW(1:1)))

    I double checked and it is entered properly with "CMD-SHIFT-ENTER".

    I'm using a MacBook Pro with "Microsoft Excel 2011 For Mac 2011", version 14.4.8 installed.

    Could anyone please help? It would be greatly appreciated.

    Thanks.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,534

    Re: Array formula suddenly stopped working

    Hi stock11r,

    Did you insert a new row at the top of your workbook to put some more information in? Did you insert any other rows or columns to make thing look better between yesterday and today?
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,259

    Re: Array formula suddenly stopped working

    Difficult to know without knowing what's in all the cells to which you refer.

    Maybe post a sample workbook.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    08-12-2005
    Posts
    7

    Re: Array formula suddenly stopped working

    Thanks Marvin for your quick reply.

    I'm 99% sure I didn't add any rows/columns. However, upon finding this error today, I traced and found a cell with a "#REF" error in Row 1, which I deleted as it served no purpose anymore. This didn't solve the problem, so I saved and closed the worksheet and re-opened.

    I also re-calc'd the sheet and made sure it is set to "Auto Calculate".

    I'm wondering if I do add rows/columns in the future if it would cause this array to have an error? Because i have several array formulas in this sheet which are still working fine between yesterday and today.

  5. #5
    Registered User
    Join Date
    08-12-2005
    Posts
    7

    Re: Array formula suddenly stopped working

    Hi TMS,

    Here is the worksheet. The formula is in the "Master" worksheet (very far to the right as you can see from the cell reference).
    Attached Files Attached Files

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,534

    Re: Array formula suddenly stopped working

    I expect the workbook is private, so here is how you can find the problem yourself.

    On the Formulas Tab there in an Icon called "Evaluate Formula". Bring up the Evaluate dialog and step though your formula to see what breaks (first?) and this might give you a chance to find an fix the problem.

    Read about it at:

    http://www.officearticles.com/excel/...soft_excel.htm

    HTH.

  7. #7
    Registered User
    Join Date
    08-12-2005
    Posts
    7

    Re: Array formula suddenly stopped working

    Hi Marvin,

    Unfortunately that feature is unavailable in Mac 2011,

    http://www.mrexcel.com/forum/excel-q...ac-2011-a.html

    I don't mind if you have a look at this worksheet. Thanks

  8. #8
    Registered User
    Join Date
    08-12-2005
    Posts
    7

    Re: Array formula suddenly stopped working

    The data in this worksheet is completely fictitious, as it hasn't been implemented yet.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,746

    Re: Array formula suddenly stopped working

    That is unfortunate that a useful debugging tool is not available.

    When I step through the function, I see that it seems to be related to the IF() function inside of the SMALL() function. I am not certain what it is supposed to be doing, but that looks like the place to start.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,259

    Re: Array formula suddenly stopped working

    I suspect that I'm not going to be able to help you, given the complexity of the workbook and fomulae. However, the first part of this formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    ,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    evaluates to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which makes me wonder if, indeed, one or more columns has been inserted. If not, it seems an odd way to refer to a two column range.


    Regards, TMS

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,259

    Re: Array formula suddenly stopped working

    Don't know if this helps ...

    J&M_Admin.jpg

  12. #12
    Registered User
    Join Date
    08-12-2005
    Posts
    7

    Re: Array formula suddenly stopped working

    TMS: You have solved it! I must have inserted a column, because when I changed the references inside of the INDIRECT functions to match the references on left side of the ":" - For example: $KW$6:INDIRECT("$KW$&$KQ$2), it fixed the problem! Thank you so much!

    I'll be careful to note when I add any columns, or find a way to automate in the future.

    MrShorty: thanks also for your help

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    34,259

    Re: Array formula suddenly stopped working

    You're welcome.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula Suddenly Stopped Working?
    By nesbensen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-26-2015, 02:28 PM
  2. Macro suddenly stopped working - not sure what has happened.
    By Blazingworm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-24-2014, 04:56 PM
  3. [SOLVED] Spin button suddenly stopped working
    By Taemex in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-19-2014, 07:23 PM
  4. [SOLVED] Vlookup function suddenly stopped working!!!!!
    By danmack in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-21-2013, 10:39 AM
  5. Replies: 3
    Last Post: 05-03-2013, 02:05 PM
  6. Code suddenly stopped working
    By bibleguy125 in forum Excel General
    Replies: 1
    Last Post: 02-15-2012, 01:00 PM
  7. Beforeclose sub suddenly stopped working
    By cuewoz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2010, 12:29 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1