very long nested IF formula problem

[andrewb90]

Hello,


Here is my current formula

=IF(Sunday!$D$23="NO",Sunday!E23,IF(Sunday!$D$26="NO",Sunday!E26,IF(Sunday!$D$29="NO",Sunday!E29,IF(Sunday!$D$32="NO",Sunday!E32,IF(Sunday!$D$35="NO",Sunday!E35,IF(Sunday!$D$38="NO",Sunday!E38,IF(Sunday!$D$41="NO",Sunday!E41,IF(Sunday!$D$44="NO",Sunday!E44,IF(Sunday!$D$47="NO",Sunday!E47,IF(Sunday!$D$50="NO",Sunday!E50,IF(Sunday!$D$53="NO",Sunday!E53,IF(Sunday!$D$56="NO",Sunday!E56,IF(Sunday!$D$59="NO",Sunday!E59,IF(Sunday!$D$62="NO",Sunday!E62,IF(Sunday!$D$65="NO",Sunday!E65,IF(Sunday!$D$68="NO",Sunday!E68,IF(Sunday!$D$71="NO",Sunday!E71,IF(Sunday!$D$74="NO",Sunday!E74,IF(Sunday!$D$77="NO",Sunday!E77,IF(Sunday!$D$80="NO",Sunday!E80,IF(Sunday!$D$83="NO",Sunday!E83,IF(Sunday!$D$86="NO",Sunday!E86,IF(Sunday!$D$89="NO",Sunday!E89,IF(Sunday!$D$92="NO",Sunday!E92,IF(Sunday!$D$95="NO",Sunday!E95,IF(Sunday!$D$98="NO",Sunday!E98,IF(Sunday!$D$101="NO",Sunday!E101,IF(Sunday!$D$104="NO",Sunday!E104,IF(Sunday!$D$107="NO",Sunday!E107,IF(Sunday!$D$110="NO",Sunday!E110,"N/A"))))))))))))))))))))))))))))))

Now, I have this formula in five consecutive rows. The problem is, that they all return the same value when I need it to skip the value on the previous row. (So for example on row 23 the formula finds a "NO" value on D65, so it puts in the value of E65 (let's say BOX #11)
now on row 24 this same formula will be in place. It would normally evaluate as the above row but I need the row excluded (in this example row 65) so that it will find the next row that has a "NO" value (lets say 74).
I need this done for all four rows that my formula is in (the first row, I would imagine would not need any changes)


Any help would be great!

Thanks,

Andrew

 

[Joyner]

Since you can't just copy down, can you try this version for each of the five cells:

=IFERROR(INDEX(Sunday!$E$23:$E$110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),1),1),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),2),1),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),3),1),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),4),1),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),5),1),"")

Or Aladin's version:

=IFERROR(INDEX(Sunday!$E$23:$E$110, SMALL(IF(MOD(ROW(Sunday!$D$23:$D$110)-ROW(Sunday!$D$23),3)=0,IF(Sunday!$D$23:$D$110="NO",ROW(Sunday!$E$23:$E$110)-ROW(Sunday!$E$23)+1)), 1)),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110, SMALL(IF(MOD(ROW(Sunday!$D$23:$D$110)-ROW(Sunday!$D$23),3)=0,IF(Sunday!$D$23:$D$110="NO",ROW(Sunday!$E$23:$E$110)-ROW(Sunday!$E$23)+1)), 2)),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110, SMALL(IF(MOD(ROW(Sunday!$D$23:$D$110)-ROW(Sunday!$D$23),3)=0,IF(Sunday!$D$23:$D$110="NO",ROW(Sunday!$E$23:$E$110)-ROW(Sunday!$E$23)+1)), 3)),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110, SMALL(IF(MOD(ROW(Sunday!$D$23:$D$110)-ROW(Sunday!$D$23),3)=0,IF(Sunday!$D$23:$D$110="NO",ROW(Sunday!$E$23:$E$110)-ROW(Sunday!$E$23)+1)), 4)),"")
=IFERROR(INDEX(Sunday!$E$23:$E$110, SMALL(IF(MOD(ROW(Sunday!$D$23:$D$110)-ROW(Sunday!$D$23),3)=0,IF(Sunday!$D$23:$D$110="NO",ROW(Sunday!$E$23:$E$110)-ROW(Sunday!$E$23)+1)), 5)),"")

 

[andrewb90]

Ill try the multiple formulas when I get home. As a thought, the fill down formula works, so what if I had five rows hidden that ran the formulas then just had my needed rows reference the cells to obtain the results. That would work right?

 

[Joyner]

Ill try the multiple formulas when I get home. As a thought, the fill down formula works, so what if I had five rows hidden that ran the formulas then just had my needed rows reference the cells to obtain the results. That would work right?

That should work, but I beleive the option of five separate formulas should work too.

 

[andrewb90]

Thank you very much! The first set of formulas have worked quite well. I made one small change:

=IFERROR(INDEX(Sunday!E23:E110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),1),1),"")

I removed the $ from the first range so I could apply it to several adjacent rows.

 

[Aladin Akyurek]

Thank you very much! The first set of formulas have worked quite well. I made one small change:

=IFERROR(INDEX(Sunday!E23:E110,SMALL(IF(Sunday!$D$23:$D$110="No",ROW($A$1:$A$79),""),1),1),"")

I removed the $ from the first range so I could apply it to several adjacent rows.

Really? Insert a few rows in front of this formula row to check.

You have data in merged cells, so I'd adopt the MOD formula already just for that reason.

 

[Aladin Akyurek]

Ill try the multiple formulas when I get home. As a thought, the fill down formula works, so what if I had five rows hidden that ran the formulas then just had my needed rows reference the cells to obtain the results. That would work right?

You can't merge formula cells, although I don't understand why the output also must be presented in merged cells.

The best option is VBA. The second best is to enter the formula I provided in E8, E11, etc. separately, where you replace the ROWS bit with 1, 2, 3, etc. These numbers can be also given in cells that correspond to the formula instances. This set up will allow you to merge the ouput cells too.