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......

G

HB

HI

HJ

JN

KI

KP

KB

IS

JG

JU

IZ

HK

JO

KJ

KQ

KC

IT

JH

JV

JA

HL

JP

KK

KR

KD

IU

JI

JW

<colgroup><col></colgroup><tbody>
</tbody>

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 ???