Nesting more than 9 IF statements through VB code

[Sebast]

Ok, so I ve been going at this for some time now and I am stuck at the nesting formulas (IF) limitation.

=IF(AND(ROWS(A$2:A2)<=$T$9;$R$9="OK");INDEX(INDIRECT("'"&$O$9&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT ("'"&$O$9&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$9&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$9&"'!$H$6"))+1);(ROWS(A$2:A2)));1);
IF(AND(ROWS(A$2:A2)<=$T$10;$R$10="OK");INDEX(INDIRECT("'"&$O$10&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$10&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$10&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$10&"'!$H$6"))+1);(ROWS(A$2:A2)-$T$9));1);
IF(AND(ROWS(A$2:A2)<=$T$11;$R$11="OK");INDEX(INDIRECT("'"&$O$11&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$11&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$11&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$11&"'!$H$6"))+1);(ROWS(A$2:A2)-$T$10));1);
.
.
.
"")))

I am sure you can see the patern, I need to nest this at least 25 more times, but i am stuck at 3 (or 6).

Any ideas on how I could get it accomplished? Maybe through VBA?

Hope there is someone who can crack this.

 

[SteveO59L]

Try posting a sample of your data, and what you want to achieve. IMHO, having 30 if statements isnt such a good idea. But, if we can see your data, someone may be able to propose a different approach.

 

[Norie]

Well perhaps I'm not seeing something, but I'm certainly not seeing a pattern in that formula.

Perhaps if you explained in words what it's meant to do, and as Steve suggested post some sample data it might help.

 

[Sebast]

First of all, hats off guys, you guys are fast.

Demonstration:

Sheet with code      Sheet A x        Sheet B             Sheet C x           ......
Sheet A R2            Row 1            Row 1                Row 1 x
Sheet A R5            Row 2 x          Row 2                Row 2 
Sheet C R1            Row 3            Row 3                Row 3 x
Sheet C R3            Row 4            Row 4                Row 4 x
Sheet C R5            Row 5 x          Row 5                Row 5

The trick is it has to be dynamic. The code I paste is on the Sheet with code. and it gets scrolled down so

the cell after

=IF(AND(ROWS(A$2:A2)<=$T$9;$R$9="OK");INDEX(INDIRECT("'"&$O$9&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$9&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$9&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$9&"'!$H$6"))+1);(ROWS(A$2:A2)));1);
IF(AND(ROWS(A$2:A2)<=$T$10;$R$10="OK");INDEX(INDIRECT("'"&$O$10&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$10&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$10&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$10&"'!$H$6"))+1);(ROWS(A$2:A2)-$T$9));1);
IF(AND(ROWS(A$2:A2)<=$T$11;$R$11="OK");INDEX(INDIRECT("'"&$O$11&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$11&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$11&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$11&"'!$H$6"))+1);(ROWS(A$2:A2)-$T$10));1);"")))

has

=IF(AND(ROWS(A$2:A3)<=$T$9;$R$9="OK");INDEX(INDIRECT("'"&$O$9&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$9&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$9&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$9&"'!$H$6"))+1);(ROWS(A$2:A3)));1);
IF(AND(ROWS(A$2:A3)<=$T$10;$R$10="OK");INDEX(INDIRECT("'"&$O$10&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$10&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$10&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$10&"'!$H$6"))+1);(ROWS(A$2:A3)-$T$9));1);
IF(AND(ROWS(A$2:A3)<=$T$11;$R$11="OK");INDEX(INDIRECT("'"&$O$11&"'!$A$6:$H$250");SMALL(IF(NOT(INDIRECT("'"&$O$11&"'!$H$6:$H$250")="");ROW(INDIRECT("'"&$O$11&"'!$H$6:$H$250"))-ROW(INDIRECT("'"&$O$11&"'!$H$6"))+1);(ROWS(A$2:A3)-$T$10));1);"")))

Now the checks for OK are just checks for including specific sheets depending whether certain criteria are met.

If someone is able to solve my problem I am glad to give you the whole file.

http://i46.tinypic.com/2nl7h9e.jpg



Here is a watered down version.

Try adding values into the yellow part on each sheet and see what happens, obviously in the same row as other categories.

http://www.filefront.com/15042019/Nesting-problem.xls/