Making column reference in INDIRECT non-static
 

In those rare instances where I need to use indirect I sometimes point the
column reference to this, for example
=IF(QUOTIENT(COLUMN($C$2)-1,26)=0,"",CHAR(QUOTIENT(COLUMN($C$2)-1,26)+96))&CHAR(IF(MOD(COLUMN($C$2),26)=0,26,MOD(C OLUMN($C$2),26))+96)
which will return C, but change to D if a column is inserted.
Is there a shorter way to do this, some formula that will change back the 3
result of =COLUMN($C$2) back into a C?
(Forgive me for showing QUOTIENT(COLUMN($C$2)-1,26), I've already learned
from seeing Bob Phillips posts that INT((COLUMN($C$2)-1)/26) requires less
typing.)

Making column reference in INDIRECT non-static
 

Hi Bob

=CHAR(COLUMN($C$2)+64) will return uppercase C which I would have
thought would be preferable.
=CHAR(COLUMN($C$2)+96) will return lowercase c

But I was wondering what the formula is that would require you to do
this, and, whether there might not be a simpler way altogether?

--
Regards

Roger Govier


"Bob Tarburton" wrote in message
...
In those rare instances where I need to use indirect I sometimes point
the column reference to this, for example
=IF(QUOTIENT(COLUMN($C$2)-1,26)=0,"",CHAR(QUOTIENT(COLUMN($C$2)-1,26)+96))&CHAR(IF(MOD(COLUMN($C$2),26)=0,26,MOD(C OLUMN($C$2),26))+96)
which will return C, but change to D if a column is inserted.
Is there a shorter way to do this, some formula that will change back
the 3 result of =COLUMN($C$2) back into a C?
(Forgive me for showing QUOTIENT(COLUMN($C$2)-1,26), I've already
learned from seeing Bob Phillips posts that INT((COLUMN($C$2)-1)/26)
requires less typing.)

Making column reference in INDIRECT non-static
 

Yes, you also need the Analysis Tool Pack to get QUOTIENT( ) whereas
INT( ) is a built-in function, readily available to everyone.

Pete

Making column reference in INDIRECT non-static
 

Yes, I guess 64 is better than 96, however, I still need my big long formula
columns AA and on.

"Roger Govier" wrote in message
...
Hi Bob

=CHAR(COLUMN($C$2)+64) will return uppercase C which I would have thought
would be preferable.
=CHAR(COLUMN($C$2)+96) will return lowercase c

But I was wondering what the formula is that would require you to do this,
and, whether there might not be a simpler way altogether?

--
Regards

Roger Govier


"Bob Tarburton" wrote in message
...
In those rare instances where I need to use indirect I sometimes point
the column reference to this, for example
=IF(QUOTIENT(COLUMN($C$2)-1,26)=0,"",CHAR(QUOTIENT(COLUMN($C$2)-1,26)+96))&CHAR(IF(MOD(COLUMN($C$2),26)=0,26,MOD(C OLUMN($C$2),26))+96)
which will return C, but change to D if a column is inserted.
Is there a shorter way to do this, some formula that will change back the
3 result of =COLUMN($C$2) back into a C?
(Forgive me for showing QUOTIENT(COLUMN($C$2)-1,26), I've already learned
from seeing Bob Phillips posts that INT((COLUMN($C$2)-1)/26) requires
less typing.)

Making column reference in INDIRECT non-static
 

Thanks for pointing out the additional advantage.

"Pete" wrote in message
oups.com...
Yes, you also need the Analysis Tool Pack to get QUOTIENT( ) whereas
INT( ) is a built-in function, readily available to everyone.

Pete