Column Letters plus 2 more columns?

[gmooney]

Hello,

I have a list of column letters that need to have 2 more columns added to the current column letters. It isn't as simple as going 2 columns away and grabbing that from my list because the column letters are in random order.

The letter G below is in my cell CJ10, HB in CJ11, etc....

I want to create a formula in cell CT10 and copied down replacing the columns letters listed below. The G would become I, the HB would become HD, the HI would become HK, etc......



[TABLE="width: 57"]
<colgroup><col></colgroup><tbody>[TR]
[TD]G[/TD]
[/TR]
[TR]
[TD]HB[/TD]
[/TR]
[TR]
[TD]HI[/TD]
[/TR]
[TR]
[TD]HJ[/TD]
[/TR]
[TR]
[TD]JN[/TD]
[/TR]
[TR]
[TD]KI[/TD]
[/TR]
[TR]
[TD]KP[/TD]
[/TR]
[TR]
[TD]KB[/TD]
[/TR]
[TR]
[TD]IS[/TD]
[/TR]
[TR]
[TD]JG[/TD]
[/TR]
[TR]
[TD]JU[/TD]
[/TR]
[TR]
[TD]IZ[/TD]
[/TR]
[TR]
[TD]HK[/TD]
[/TR]
[TR]
[TD]JO[/TD]
[/TR]
[TR]
[TD]KJ[/TD]
[/TR]
[TR]
[TD]KQ[/TD]
[/TR]
[TR]
[TD]KC[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[/TR]
[TR]
[TD]JH[/TD]
[/TR]
[TR]
[TD]JV[/TD]
[/TR]
[TR]
[TD]JA[/TD]
[/TR]
[TR]
[TD]HL[/TD]
[/TR]
[TR]
[TD]JP[/TD]
[/TR]
[TR]
[TD]KK[/TD]
[/TR]
[TR]
[TD]KR[/TD]
[/TR]
[TR]
[TD]KD[/TD]
[/TR]
[TR]
[TD]IU[/TD]
[/TR]
[TR]
[TD]JI[/TD]
[/TR]
[TR]
[TD]JW[/TD]
[/TR]
</tbody>[/TABLE]


Any help out there?

Thanks

 

[DanteAmor]

Put the fórmula:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(CJ10&1))+2,4),"1","")

 

[Michael M]

OR a VBA solution

Sub MM1()
Dim TCL As Integer, TCR As Variant, r As Long
    For r = 10 To 38
        TCL = Range(Range("CJ" & r).Value & 1).Column + 2
        TCR = Split(Cells(1, TCL).Address, "$")(1)
        Range("CT" & r).Value = TCR
    Next r
End Sub

 

[gmooney]

Thank you....worked like a charm!

 

[Michael M]

Which method did you end up using ???