I have the following formula,
=IF(ISERROR(INDEX('AD Initial'!A$2:A$5001,SMALL(IF('AD Initial'!$H$2:$H$5001="NA",ROW('AD Initial'!A$2:A$5001)),ROW(1:1))-1,1)),"",INDEX('AD Initial'!A$2:A$5001,SMALL(IF('AD Initial'!$H$2:$H$5001="NA",ROW('AD Initial'!$H$2:$H$5001)),ROW(1:1))-1,1))&""
It works a treat by checking another work sheet and orders each row depending on the value in column H.
The code above will pick out any NAs and pull them onto the current work sheet, I have others for OPEN, Repeat, One-off, Closed etc all auto sorting the data to relevant sheets.
Now this is great and all but I have come to find spill formulas are more efficient than volitile formulas so was looking to see if the above could have a spill alternative?
=IF(ISERROR(INDEX('AD Initial'!A$2:A$5001,SMALL(IF('AD Initial'!$H$2:$H$5001="NA",ROW('AD Initial'!A$2:A$5001)),ROW(1:1))-1,1)),"",INDEX('AD Initial'!A$2:A$5001,SMALL(IF('AD Initial'!$H$2:$H$5001="NA",ROW('AD Initial'!$H$2:$H$5001)),ROW(1:1))-1,1))&""
It works a treat by checking another work sheet and orders each row depending on the value in column H.
The code above will pick out any NAs and pull them onto the current work sheet, I have others for OPEN, Repeat, One-off, Closed etc all auto sorting the data to relevant sheets.
Now this is great and all but I have come to find spill formulas are more efficient than volitile formulas so was looking to see if the above could have a spill alternative?