Can I get TextJoin to not break the Column?

[David Hartmann]

I have a multi-sheet workbook that I am trying to use TextJoin.  I have success using the TextJoin function like this =TEXTJOIN(", ",TRUE,Matrix!E2:E3000).  I am trying to copy the columns into rows in another sheet.  However, click and dragging does not change the column how I thought it would.  I used the formula =TEXTJOIN(", ",TRUE,Matrix!E$2:E$3000), and that keeps every thing the same.  Can I get it to switch columns only, while keeping my rows the same?  I tried using the OFFSET function with no luck.

[#2]

If I understand you want to be able to drag your textjoin down, and have it join text from +1 column over on your matrix worksheet?

I think you were on the right track with offset, you just need a find a way to increment the column offset. If we use row() inside the offset then we can use that to move the target right as you drag the formula down.

=TEXTJOIN(", ",TRUE,OFFSET(Matrix!$A$1,0,ROW()-1,3000))

This assumes that this formula is pasted into A1 on your second sheet, and that the data you want to combine is also in A1 on the Matrix sheet. If thats not quite right then just adapt this to fit.

I've attached a sheet with it working for you to look at.