formula converting number to column letter 26
 

I've always used A1 notation in my formulas as well as VBA. When I need to
select a range from anything higher than column Z, I generally use VBA to
convert to a multi-letter column ID.

I'm working in someone else's workbook, and want to avoid VBA/macros (and
might as well learn something new). The existing formula is:

=INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781")

and now that the data has hit more than 26 columns, it is throwing a
reference error. Is there a simple replacement to reference any column's
alpha ID, even the 2-digit IDs? If not, what would be the appropriate way to
replicate the above statement with R1C1 notation (I'm assuming I can use
R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
there are hundreds of formulas I don't want to change)

Thanks!
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

formula converting number to column letter 26
 

Hi Keith,

INDIRECT() has an optional 2nd argument which allows R1C1 reference style

--
Kind regards,

Niek Otten

"KR" wrote in message
...
I've always used A1 notation in my formulas as well as VBA. When I need to
select a range from anything higher than column Z, I generally use VBA to
convert to a multi-letter column ID.

I'm working in someone else's workbook, and want to avoid VBA/macros (and
might as well learn something new). The existing formula is:

=INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781")

and now that the data has hit more than 26 columns, it is throwing a
reference error. Is there a simple replacement to reference any column's
alpha ID, even the 2-digit IDs? If not, what would be the appropriate way
to
replicate the above statement with R1C1 notation (I'm assuming I can use
R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
there are hundreds of formulas I don't want to change)

Thanks!
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

formula converting number to column letter 26
 

Thank Niek!
I think I was having a brain-dead moment when I was looking for a syntax
change; I just remembered that I could use an offset command and get
anywhere I need to be..
:)

Thanks,
keith

"Niek Otten" wrote in message
...
Hi Keith,

INDIRECT() has an optional 2nd argument which allows R1C1 reference style

--
Kind regards,

Niek Otten

"KR" wrote in message
...
I've always used A1 notation in my formulas as well as VBA. When I need
to
select a range from anything higher than column Z, I generally use VBA
to
convert to a multi-letter column ID.

I'm working in someone else's workbook, and want to avoid VBA/macros
(and
might as well learn something new). The existing formula is:

=INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781")

and now that the data has hit more than 26 columns, it is throwing a
reference error. Is there a simple replacement to reference any column's
alpha ID, even the 2-digit IDs? If not, what would be the appropriate
way
to
replicate the above statement with R1C1 notation (I'm assuming I can use
R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
there are hundreds of formulas I don't want to change)

Thanks!
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.

formula converting number to column letter 26
 

Keith

From your formula I understand that InfoLists!D19 currently contains
the numbers 0-25, to reference columns 1-26. With this philosophy, the
following formula should work:

=INDIRECT(("RawOut!" & IF(InfoLists!D19<26,CHAR(InfoLists!D19+1+64) &
"1",
CHAR(INT(InfoLists!D19/26)+64)&CHAR(MOD(InfoLists!D19,26)+1+64)&"781"))

HTH
Kostis Vezerides

formula converting number to column letter 26
 

Lol, this shows how a very complicated way of thinking can save you
seconds of work...

formula converting number to column letter 26
 

=INDIRECT(ADDRESS(781,D19+1))

HTH
--
AP

"KR" a écrit dans le message de
...
I've always used A1 notation in my formulas as well as VBA. When I need to
select a range from anything higher than column Z, I generally use VBA to
convert to a multi-letter column ID.

I'm working in someone else's workbook, and want to avoid VBA/macros (and
might as well learn something new). The existing formula is:

=INDIRECT("RawOut!" & CHAR(InfoLists!D19+1+64) & "781")

and now that the data has hit more than 26 columns, it is throwing a
reference error. Is there a simple replacement to reference any column's
alpha ID, even the 2-digit IDs? If not, what would be the appropriate way
to
replicate the above statement with R1C1 notation (I'm assuming I can use
R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
there are hundreds of formulas I don't want to change)

Thanks!
Keith

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.