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.
=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.