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

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

[Bob Phillips]

I think you mean

=INDIRECT(A1&7)

where A1 holds the letter in this instance.

--
HTH

Bob Phillips

"Dmitry Kopnichev" <[email protected]> wrote in message
news:%[email protected]...
> 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)?
>
>

[Dmitry Kopnichev]

Thanks for your reply.
How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
automatically?
"Bob Phillips" <[email protected]> сообщил/сообщила в
новостях следующее: news:[email protected]...
> I think you mean
>
> =INDIRECT(A1&7)
>
> where A1 holds the letter in this instance.
>
> --
> HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <[email protected]> wrote in message
> news:%[email protected]...
> > 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)?
> >
> >
>
>

[Bob Phillips]

=INDIRECT(A1&ROW())

--
HTH

Bob Phillips

"Dmitry Kopnichev" <[email protected]> wrote in message
news:%234JbQ%[email protected]...
> Thanks for your reply.
> How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
> automatically?
> "Bob Phillips" <[email protected]> сообщил/сообщила в
> новостях следующее: news:[email protected]...
> > I think you mean
> >
> > =INDIRECT(A1&7)
> >
> > where A1 holds the letter in this instance.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Dmitry Kopnichev" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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)?
> > >
> > >
> >
> >
>
>

[Dmitry Kopnichev]

Thanks!
How to convert a column number to a corresponding column label, letter?
"Bob Phillips" <[email protected]> сообщил/сообщила в
новостях следующее: news:[email protected]...
> =INDIRECT(A1&ROW())
>
> --
> HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <[email protected]> wrote in message
> news:%234JbQ%[email protected]...
> > Thanks for your reply.
> > How to change =C1 (any reference) to =INDIRECT(A1&1(the same row number)
> > automatically?
> > "Bob Phillips" <[email protected]> сообщил/сообщила в
> > новостях следующее: news:[email protected]...
> > > I think you mean
> > >
> > > =INDIRECT(A1&7)
> > >
> > > where A1 holds the letter in this instance.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Dmitry Kopnichev" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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)?
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[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

--
HTH

Bob Phillips

"Dmitry Kopnichev" <[email protected]> wrote in message
news:[email protected]...
> Thanks!
> How to convert a column number to a corresponding column label, letter?
> "Bob Phillips" <[email protected]> сообщил/сообщила в
> новостях следующее: news:[email protected]...
> > =INDIRECT(A1&ROW())
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Dmitry Kopnichev" <[email protected]> wrote in message
> > news:%234JbQ%[email protected]...
> > > Thanks for your reply.
> > > How to change =C1 (any reference) to =INDIRECT(A1&1(the same row
number)
> > > automatically?
> > > "Bob Phillips" <[email protected]> сообщил/сообщила в
> > > новостях следующее: news:[email protected]...
> > > > I think you mean
> > > >
> > > > =INDIRECT(A1&7)
> > > >
> > > > where A1 holds the letter in this instance.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Dmitry Kopnichev" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > 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)?
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

[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.
"Bob Phillips" <[email protected]> сообщил/сообщила в
новостях следующее: news:[email protected]...
> 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
>
> --
> HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks!
> > How to convert a column number to a corresponding column label, letter?
> > "Bob Phillips" <[email protected]> сообщил/сообщила в
> > новостях следующее: news:[email protected]...
> > > =INDIRECT(A1&ROW())
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Dmitry Kopnichev" <[email protected]> wrote in message
> > > news:%234JbQ%[email protected]...
> > > > Thanks for your reply.
> > > > How to change =C1 (any reference) to =INDIRECT(A1&1(the same row
> number)
> > > > automatically?
> > > > "Bob Phillips" <[email protected]> сообщил/сообщила
в
> > > > новостях следующее: news:[email protected]...
> > > > > I think you mean
> > > > >
> > > > > =INDIRECT(A1&7)
> > > > >
> > > > > where A1 holds the letter in this instance.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Dmitry Kopnichev" <[email protected]> wrote in message
> > > > > news:%[email protected]...
> > > > > > 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)?
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>