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

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" <reply2bob@_removethis_intergate.com> wrote in message
news:[email protected]...
> 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.)
>

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

Pete

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

"Roger Govier" <[email protected]> wrote in message
news:[email protected]...
> 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" <reply2bob@_removethis_intergate.com> wrote in message
> news:[email protected]...
>> 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.)
>>
>
>

Thanks for pointing out the additional advantage.

"Pete" <[email protected]> wrote in message
news:[email protected]...
> Yes, you also need the Analysis Tool Pack to get QUOTIENT( ) whereas
> INT( ) is a built-in function, readily available to everyone.
>
> Pete
>