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

 

[RonB1111]

Would you post a small sample of your data, about a dozen consecutive rows or so. Also please clarify if the formula is exactly the same in your 5 consecutive rows or does the E23 change to E24, E25, etc.

It appears your are trying to return the 1st 5 values in E where there's a "NO" in Col D - is this correct?

 

[ukmikeb]

Hi

Sorry mis-read question

 

[andrewb90]

Hi Ron,

I'll get a sample posted for you. In the meantime, The formula will be exactly the same for each of the consecutive rows. (the cells in the formula are every third cell though)
Yes, I am trying to return the first five values (or less in some cases) in E where there's a "NO" in D. They are not always likely to be 5 in a row. As in my example above they may sit 5-20 rows apart between each value that I am searching for.

 

[Joyner]

Hello, IF I understand what you need, try this array formala, copied down your five rows, or as far as you need. So confirm with Ctrl+Shift+Enter, not just enter:

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

 

[andrewb90]

I get an error message saying that the formula is not valid in a merged cell. Any way around this?

 

[Joyner]

I would have to see your data/layout, but as everyone here on this board will tell you, avoid merged cells.

 

[Aladin Akyurek]

A2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sunday!$E$23:$E$1100,
  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)),
  ROWS($A$2:A2))),"")

 

[andrewb90]

This formula works when I fill down, but when I try to insert the formula in each cell individually (So I can remerge them) The formula no longer works.
The cells that the formulas are going in are E8, E11, E14, E17, and E20

 

[Aladin Akyurek]

This formula works when I fill down,

Exactly as designed.

but when I try to insert the formula in each cell individually (So I can remerge them) The formula no longer works.
The cells that the formulas are going in are E8, E11, E14, E17, and E20

You should sollicite for code in VBA that implements the formula in cells you list.