How to replace column letter in refferences with a function using the old column letter?

D

Dmitry Kopnichev

Hello
How to replace column letter(s) (or column numbers) in refferences with a
result of a function using the old column letter(s) (or column numbers)?
 
D

Dmitry Kopnichev

Thanks for your reply.
How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
automatically?
 
B

Bob Phillips

=INDIRECT(A1&ROW())

--
HTH

Bob Phillips

Dmitry Kopnichev said:
Thanks for your reply.
How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
automatically?
with
Click to expand...
 
D

Dmitry Kopnichev

Thanks!
How to convert a column number to a corresponding column label, letter?
 
B

Bob Phillips

I use a simple UDF

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function
 
D

Dmitry Kopnichev

Thanks.
I have to make a 2004 year calculation using 2004 year table in the same way
a 2003 year calculation which used 2003 year table was made. I copied
formulas from 2003 version table to 2004 version table. The 2004 version
table has different columns order therefore column letters in the formulas
are incorrect. I want to change the column letters to correct ones according
to 2003 and 2004 column lables located in a top row in the tables. I have to
use columns with the same lables as in 2003 year in formulas. How to change
the column letters to correct ones in all cells automatically? I do not need
to change row numbers because they are correct.
 
Top