Excel – Dynamic Excel Formulas For Ranges

microsoft excelmicrosoft-excel-2016worksheet-function

  • How can I make a formula that:

    1. I can vertically drag down on Sheet2…
    2. That TEXTJOINS two arrays (total of 28 vertical cells) of data on Sheet1…
    3. Which after it TEXTJOINS those 28 cells, goes to the next row in Sheet2…
    4. Looks at the same column but different row numbers then after it does this 8 times in total…
    5. Changes to the next column while looking at the same rows from iteration 1.

  • In short, I want a formula that keeps the column letter static for 8 rows then changes to the next column after those 8 rows.

  • The referenced image below should make it clearer of the objective I'm going for.

Referenced Image

An alternative if this is impossible/hard is to code a python script that types the formulas with using loops then I'll just copying those to excel.
Much appreciated!

Best Answer

You can use the INDEX function, with mathematical arguments for the row and column, to return the desired ranges.

For example:

MOD(ROWS($1:1)-1,8)*13+122

will generate the repeating series: 122,135,148,161...213

and 

=INT((ROWS($1:1)-1)/8)+1

will generate the repeating series 1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2, …

So to put that in a formula, which will change the ranges as you state, for, let us say, columns B:D, you could use:

=TEXTJOIN(,TRUE,INDEX(prepVertical!$B:$D,MOD(ROWS($1:1)-1,8)*13+122,INT((ROWS($1:1)-1)/8)+1):INDEX(prepVertical!$B:$D,MOD(ROWS($1:1)-1,8)*13+134,INT((ROWS($1:1)-1)/8)+1))

If you need to reference more columns, just change B:D to refer to the desired column range.

Related Question