Return targeted columns from an array of columns

**Pauleyb** · 10-09-2013, 03:18 PM

Okay, I know this is not the most elegant solution (uses OFFSET and is an array formula for a large spreadsheet), but I thought it was a interesting challenge. This is what worked. Put this in cell A1 of Sheet2:
=OFFSET(Sheet1!$A$1,ROW(),SMALL(--(Sheet1!$A$1:$AX$1)*COLUMN(Sheet1!$A$1:$AX$1),COUNTIF(Sheet1!$B$1:$AX$1,FALSE)+COLUMN()+1)-1)
Enter with Ctrl-Shift-Enter, and then drag across and down the sheet.

**tuna666** · 10-10-2013, 06:32 AM

Thank you Pauley, I've tried your formula but it just returned VALUE! in all cells. Thank you anyways, my issue is now solved ^_^

Originally Posted by Pauleyb

Okay, I know this is not the most elegant solution (uses OFFSET and is an array formula for a large spreadsheet), but I thought it was a interesting challenge. This is what worked. Put this in cell A1 of Sheet2:
=OFFSET(Sheet1!$A$1,ROW(),SMALL(--(Sheet1!$A$1:$AX$1)*COLUMN(Sheet1!$A$1:$AX$1),COUNTIF(Sheet1!$B$1:$AX$1,FALSE)+COLUMN()+1)-1)
Enter with Ctrl-Shift-Enter, and then drag across and down the sheet.