formula is not working; however, it worked on previous versions of the same file

Hello,

I currently have a workbook that has several sheets and one of the array formulas has quit working. In previous version of the workbook the formula works. I had to add columns to the sheet that contains the formula but that's the only change that I've made. The formula actually works on a few lines but then it stops. I've gone through the workbook and cleared the contents in columns and row that I'm not using, I've gone back to a version of the file that worked, I've gone into each of the cells and hit Ctrl + Shft - and I've closed and reopened Excel in hopes to figure out the problem but no luck. Below is the formula that's causing me such pain (it is repeated down to 2600 rows)

=IFERROR(INDEX(EN$5:EN$2298,SMALL((IF(LEN(EN$5:EN$2298),ROW(INDIRECT("1:"&ROWS(EP5:EP$2298))))),ROW(A1)),1),"")
=IFERROR(INDEX(EN$5:EN$2298,SMALL((IF(LEN(EN$5:EN$2298),ROW(INDIRECT("1:"&ROWS(EP6:EP$2298))))),ROW(A2)),1),"")
=IFERROR(INDEX(EN$5:EN$2298,SMALL((IF(LEN(EN$5:EN$2298),ROW(INDIRECT("1:"&ROWS(EP7:EP$2298))))),ROW(A3)),1),"")

formula works up to the 3rd role - and nothing after that.

PLEASE HELP - I've been working on this for hours and have tries several ways of completing it.

Regards,

You lost me at IF(LEN(EN$5:EN$2298)..

Under what condition is this true?

Not sure of what you are asking.

The purpose of this formula is to look at column EN5 through EN2298 and create a list. Column EN is contains IF statements based on several cells and if there is a result is will be populated if not the cell will be a blank. I can't have any blank spaces in the list so that is why column EN has the formula listed above. The LEN portion of the fomula was given to me - was told that I needed it to make sure the list would be in numerical / alpha order and that it would eliminate any blank cells.


Hope this helps.

I can't help but feel as though the expression is missing a term.

The statement appears to read as "IF THE LENGTH OF EN5 TO EN2298" but there's no qualifying statement.

It's not being compared to anything. If you are able to desensitize your information and post an example, it would be considerably easier to troubleshoot what went wrong than to picture it in our heads.