Making column reference in INDIRECT non-static

[Bob Tarburton]

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(COLUMN($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.)

 

[Roger Govier]

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?

 

[Pete]

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

Pete

 

[Bob Tarburton]

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

 

[Bob Tarburton]

Thanks for pointing out the additional advantage.