formula converting number to column letter >26

[KR]

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

 

[Niek Otten]

Hi Keith,

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

 

[KR]

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

 

[vezerid]

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

 

[vezerid]

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

 

[Ardus Petus]

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

HTH