INDEX( SMALL( IF(... resulting in #NUM today, yesterday was correct

1

{=INDEX(Sheet1!$A:$M,SMALL(IF(Sheet1!$O:$O=TRUE,ROW(Sheet1!$O:$O)),ROW(1:1)),1)}

Yesterday it was full of useful values today there are #NUM errors in every cell. Not sure what went awry? Any thoughts?

The "TRUE" that is being checked for is based on a logic table (Column O) that checks to see if 4 columns meet the criteria. I did it this way because of the issues with AND/OR in array formulas. And I'm not that good with excel yet. This is the code in Column O on Sheet 1. 

=IF(AND(OR($I:$I="D",$I:$I="none",$I:$I="NA"),
        OR($J:$J="D",$J:$J="none",$J:$J="NA"),
        OR($K:$K="D",$K:$K="none",$K:$K="NA"),
        OR($L:$L="D",$L:$L="none",$L:$L="NA")),
     TRUE,FALSE)

So, if this is TRUE give me the row where the TRUE was found and creates an array of rows starting with the 1st one (ROW(1:1)). Then it gives me the corresponding value in the 1st column of the INDEX range.

Jo So

Posted 2015-09-16T13:35:21.777

Reputation: 19

Has the data in the referenced cells changed? – Dave – 2015-09-16T13:51:44.260

The report runs each time I open the spreadsheet. It looks like one additional line was added to the bottom. But Sheet 2 should be updating along with it, no? – Jo So – 2015-09-16T13:55:47.130

Depends on the value in the new cell and how the formula responds to it. EG, if you're expecting a numeric value and some one has entered a string, then the entire thing is likely to fail – Dave – 2015-09-16T14:33:18.287

Answers

0

The original formula was an array formula. That is, to work properly it must be entered using the Ctrl+Shift+Enter keystroke combination. When entered properly, the formula will appear enclosed in {=...} in the formula bar. If this is missing, then you have identified the problem.

Array formulas are annoyingly finicky. If you ever double-click a cell containing an array formula or click into the formula bar with such a cell selected and then press Enter, you will have removed the array entry and broken the formula. This explains how a formula that was working one minute may have stopped working seemingly for no reason.

To fix this, select the cell containing the formula, click into the formula bar, and then press Ctrl+Shift+Enter.

Excellll

Posted 2015-09-16T13:35:21.777

Reputation: 11 857

Sorry for the confusion. The above code now has curly braces. I have entered this using CTRL+SHIFT+ENTER initially and each time that I made edits to it. – Jo So – 2015-09-16T14:06:09.337

Asked: 2015-09-16T13:35:21.777

Viewed: 380 times

Active: 2015-09-16T14:04:17.893