software

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(quot;RawOut!quot; amp; CHAR(InfoLists!D19 1 64) amp; quot;781quot;)

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

quot;KRquot; gt; wrote in message
...
gt; I've always used A1 notation in my formulas as well as VBA. When I need to
gt; select a range from anything higher than column Z, I generally use VBA to
gt; convert to a multi-letter column ID.
gt;
gt; I'm working in someone else's workbook, and want to avoid VBA/macros (and
gt; might as well learn something new). The existing formula is:
gt;
gt; =INDIRECT(quot;RawOut!quot; amp; CHAR(InfoLists!D19 1 64) amp; quot;781quot;)
gt;
gt; and now that the data has hit more than 26 columns, it is throwing a
gt; reference error. Is there a simple replacement to reference any column's
gt; alpha ID, even the 2-digit IDs? If not, what would be the appropriate way
gt; to
gt; replicate the above statement with R1C1 notation (I'm assuming I can use
gt; R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
gt; there are hundreds of formulas I don't want to change)
gt;
gt; Thanks!
gt; Keith
gt;
gt; --
gt; The enclosed questions or comments are entirely mine and don't represent
gt; the
gt; thoughts, views, or policy of my employer. Any errors or omissions are my
gt; own.
gt;
gt;
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

quot;Niek Ottenquot; gt; wrote in message
...
gt; Hi Keith,
gt;
gt; INDIRECT() has an optional 2nd argument which allows R1C1 reference style
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;KRquot; gt; wrote in message
gt; ...
gt; gt; I've always used A1 notation in my formulas as well as VBA. When I need
to
gt; gt; select a range from anything higher than column Z, I generally use VBA
to
gt; gt; convert to a multi-letter column ID.
gt; gt;
gt; gt; I'm working in someone else's workbook, and want to avoid VBA/macros
(and
gt; gt; might as well learn something new). The existing formula is:
gt; gt;
gt; gt; =INDIRECT(quot;RawOut!quot; amp; CHAR(InfoLists!D19 1 64) amp; quot;781quot;)
gt; gt;
gt; gt; and now that the data has hit more than 26 columns, it is throwing a
gt; gt; reference error. Is there a simple replacement to reference any column's
gt; gt; alpha ID, even the 2-digit IDs? If not, what would be the appropriate
way
gt; gt; to
gt; gt; replicate the above statement with R1C1 notation (I'm assuming I can use
gt; gt; R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
gt; gt; there are hundreds of formulas I don't want to change)
gt; gt;
gt; gt; Thanks!
gt; gt; Keith
gt; gt;
gt; gt; --
gt; gt; The enclosed questions or comments are entirely mine and don't represent
gt; gt; the
gt; gt; thoughts, views, or policy of my employer. Any errors or omissions are
my
gt; gt; own.
gt; gt;
gt; gt;
gt;
gt;
Keith

gt;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((quot;RawOut!quot; amp; IF(InfoLists!D19lt;26,CHAR(InfoLists!D19 1 64) amp;
quot;1quot;,
CHAR(INT(InfoLists!D19/26) 64)amp;CHAR(MOD(InfoLists!D19,26) 1 64)amp;quot;781quot;))

HTH
Kostis VezeridesLol, this shows how a very complicated way of thinking can save you
seconds of work...=INDIRECT(ADDRESS(781,D19 1))

HTH
--
AP

quot;KRquot; gt; a écrit dans le message de
...
gt; I've always used A1 notation in my formulas as well as VBA. When I need to
gt; select a range from anything higher than column Z, I generally use VBA to
gt; convert to a multi-letter column ID.
gt;
gt; I'm working in someone else's workbook, and want to avoid VBA/macros (and
gt; might as well learn something new). The existing formula is:
gt;
gt; =INDIRECT(quot;RawOut!quot; amp; CHAR(InfoLists!D19 1 64) amp; quot;781quot;)
gt;
gt; and now that the data has hit more than 26 columns, it is throwing a
gt; reference error. Is there a simple replacement to reference any column's
gt; alpha ID, even the 2-digit IDs? If not, what would be the appropriate way
to
gt; replicate the above statement with R1C1 notation (I'm assuming I can use
gt; R1C1 in only 3 cells and leave the rest of the workbook in A1 notation,
gt; there are hundreds of formulas I don't want to change)
gt;
gt; Thanks!
gt; Keith
gt;
gt; --
gt; The enclosed questions or comments are entirely mine and don't represent
the
gt; thoughts, views, or policy of my employer. Any errors or omissions are my
gt; own.
gt;
gt;