Auto calculate on output cell not working

[vivek456]

I have the below formula embedded in cell C285 and the output in cell C285 is perfect however when I drag this formula down, I get the #NUM error message in cell C286.

When I do a manual calculation using "Calculate now" via the Tab: Formula; the output is being calculated on cell C286 perfectly. I think I have broken my head trying to figure this one out why it is not auto calculating on dragging the formula down. I have also selected "Automatic" on "Calculations options" as well.

=SMALL(IFERROR(SEARCH(,$B$285:$B$292), 2^32)+ROW($B$285:$B$292)/1000,ROWS($C$285:C285))

Any help or feedback on this, would be much appreciated.

Thanks in advance for your advice.

 

[steve the fish]

This:

IFERROR(SEARCH(,$B$285:$B$292), 2^32)+ROW($B$285:$B$292)/1000

will only produce one value if not entered CTRL-SHIFT-ENTER so when you drag down you are asking for the 2nd smallest value which doesnt exist. Ive no idea why you can get that to work by asking the sheet to calculate. It shouldnt work as far as i can tell.