Help! Can I add the index or offset function to my textjoin formula? I need to skip every other column

[Linzexcel]

I have a workbook that unfortunately has merged cells. I am using the textjoin function to get multiple lines of data into one cell (on another sheet) but i need to drag this formula down but had it skip every other column in order to skip empty/merged ones. I have tried adding the index function and the offset function to the textjoin formula i have set up but it is NOT working. Is this even possible to do? Can you add the ability to skip columns or rows to any formula? Thank you!

 

[Linzexcel]

WAIT! I got it! omg....thank you so much. I was all wrong to change it to COLUMNS. I changed it back to ROWS because I saw the error that I made in the formula somewhere else! Thank you SO much!!!

 

[KRice]

Why did you change ROWS to COLUMNS? The purpose behind ROWS(AA$27:AA27)*2-1 is so that as you drag the formula down, ROWS(AA$27:AA27)*2-1 will return 1,3,5, etc., which are the column indexes that you want: for example, the 1st column in the range R$27:Z$61 (i.e., column R) then the 3rd column in the range R$27:Z$61 (i.e., column T), etc. If you got a #REF! error, something else is wrong.

 

[Linzexcel]

You are correct, it was definitely an error on my part, lack of knowledge for sure haha. But this has been a learning experience and I really appreciate your help and time! But once I went back to ROWS and saw where the other error was, it is working perfectly!

 

[KRice]

That’s great to hear. I’m glad you got it working…and I’m happy to help.