How to change column instead of row references when dragging a formula down?

[TacticalExcel]

Apologies if a similar situation to mine has already been posted. I searched and wasn't able to find anything. I am using Excel 2016.

I have a very long formula (much of which is shown below) and I want to drag this formula down abut 100 rows with each row moving the column reference one to the right.

In other words as I drag the formula down one row I would like all references to column T to change to U and so on.

An offset formula might work, but then I would have to enter it individually for each IF statement correct?

Any easy method to switch all column references would also work, even if it doesn't change automatically as I drag the formula.

I am trying to avoid having to manually change every single column reference in the formula. Thanks!


"&" "&$B$4&" "&"include:"&" "&IF(IF(T$7="", "", T$7)&IF(T$8="", "", ", "&T$8)&IF(T$9="", "", ", "&T$9)&IF(T$10="", "", ", "&T$10)&IF(T$11="", "", ", "&T$11)&IF(T$12="", "", ", "&T$12)&IF(T$13="", "", ", "&T$13)&IF(T$14="", "", ", "&T$14)&IF(T$15="", "", ", "&T$15)&IF(T$16="", "", ", "&T$16)&IF(T$17="", "", ", "&T$17)&IF(T$28="", "", ", "&T$28)&IF(T$29="", "", ", "&T$29)&IF(T$30="", "", ", "&T$30)&IF(T$31="", "", ", "&T$31)&IF(T$32="", "", ", "&T$32)&IF(T$33="", "", ", "&T$33)&IF(T$34="", "", ", "&T$34)&IF(T$35="", "", ", "&T$35)&IF(T$36="", "", ", "&T$36)&IF(T$37="", "", ", "&T$37)&IF(T$38="", "", ", "&T$38)="", "None", IF(T$7="", "", T$7)&IF(T$8="", "", ", "&T$8)&IF(T$9="", "", ", "&T$9)&IF(T$10="", "", ", "&T$10)&IF(T$11="", "", ", "&T$11)&IF(T$12="", "", ", "&T$12)&IF(T$13="", "", ", "&T$13)&IF(T$14="", "", ", "&T$14)&IF(T$15="", "", ", "&T$15)&IF(T$16="", "", ", "&T$16)&IF(T$17="", "", ", "&T$17)&IF(T$28="", "", ", "&T$28)&IF(T$29="", "", ", "&T$29)&IF(T$30="", "", ", "&T$30)&IF(T$31="", "", ", "&T$31)&IF(T$32="", "", ", "&T$32)&IF(T$33="", "", ", "&T$33)&IF(T$34="", "", ", "&T$34)&IF(T$35="", "", ", "&T$35)&IF(T$36="", "", ", "&T$36)&IF(T$37="", "", ", "&T$37)&IF(T$38="", "", ", "&T$38))

 

[Fluff]

Any easy method to switch all column references would also work, even if it doesn't change automatically as I drag the formula.

You can use Search/Replace
Select the cell Ctrl+H Find T$ Replace U$

 

[inactiveuserps07]

@TacticalExcel, on which Row number will your results begin?

Also, you seem to be missing something at the beginning of your lengthy formula, since I see a solitary double-quote with the concatenating ampersand (&) after it to start things off; this doesn't make sense to me.

 

[inactiveuserps07]

Also, @TacticalExcel, are the expected values in your concatenation ranges text or number values?