Array formula suddenly stopped working

[stock11r]

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.

[MarvinP]

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?

[TMS]

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

Maybe post a sample workbook.

Regards, TMS

[stock11r]

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.

[stock11r]

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).

[MarvinP]

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.

[stock11r]

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

[stock11r]

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

[MrShorty]

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.

[TMS]

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:

Please Login or Register  to view this content.

,

Please Login or Register  to view this content.

evaluates to

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

[TMS]

Don't know if this helps ...

J&M_Admin.jpg

[stock11r]

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

[TMS]

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.