Forum Discussion
Solved
Filter Function Dynamic Arrays
I have a large filter function: =SORT(FILTER(FILTER(ANALYSIS!$A$3:$E$20000,IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0)>=LARGE(IF(ANALYSIS!$A$3:$A$...
- Solved! I wrapped an Index formula around it referencing cell B2 and it worked a treat!
=INDEX(SORT(FILTER(FILTER(ANALYSIS!$A$3:$E$20000,IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0)>=LARGE(IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0),Input!$B$2)),{0,1,1,0,1}),3,-1),SEQUENCE(Input!$B$2),{1,2,3})
Solved! I wrapped an Index formula around it referencing cell B2 and it worked a treat!
=INDEX(SORT(FILTER(FILTER(ANALYSIS!$A$3:$E$20000,IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0)>=LARGE(IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0),Input!$B$2)),{0,1,1,0,1}),3,-1),SEQUENCE(Input!$B$2),{1,2,3})
=INDEX(SORT(FILTER(FILTER(ANALYSIS!$A$3:$E$20000,IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0)>=LARGE(IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0),Input!$B$2)),{0,1,1,0,1}),3,-1),SEQUENCE(Input!$B$2),{1,2,3})