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.

Hi Keith,

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

--
Kind regards,

Niek Otten

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

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" <[email protected]> wrote in message
news:eTTg%[email protected]...
> Hi Keith,
>
> INDIRECT() has an optional 2nd argument which allows R1C1 reference style
>
> --
> Kind regards,
>
> Niek Otten
>
> "KR" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.
> >
> >
>
>

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

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

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

HTH
--
AP

"KR" <[email protected]> a écrit dans le message de
news:%[email protected]...
> 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.
>
>