formula for column letters
 

I need to copy/paste a sheet from one area to another sheet. I want to mark
the columns as the letters where they were before movement. I can make the
formula

="["&CHAR(64+COLUMN(Z1)-1)&"]"

to get up to column Z (the "-1" is because I've moved the columns over by
one to the right...so the B column, I need to show as A, but this is a minor
point). I cannot get this formula tweaked to where it will serve A-Z AND AA
and beyond (since the CHAR does not recognize AA as the next logical step).

Any help? Guessing it's an if/then, but not sure how.
--
Boris

formula for column letters
 

see my page http://www.mvps.org/dmcritchie/excel/join.htm
look for the macro MarkCells
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"BorisS" wrote in message ...
I need to copy/paste a sheet from one area to another sheet. I want to mark
the columns as the letters where they were before movement. I can make the
formula

="["&CHAR(64+COLUMN(Z1)-1)&"]"

to get up to column Z (the "-1" is because I've moved the columns over by
one to the right...so the B column, I need to show as A, but this is a minor
point). I cannot get this formula tweaked to where it will serve A-Z AND AA
and beyond (since the CHAR does not recognize AA as the next logical step).

Any help? Guessing it's an if/then, but not sure how.
--
Boris

formula for column letters
 

Hi Boris

=IF(COLUMN(A1)26,
CHAR(INT(COLUMN(A1)/26)+64)&CHAR(MOD(COLUMN(A1),26)+64),
CHAR(COLUMN(A1)+64))

will deal with column letters going beyond 26 (column Z)
Adapt to suit your needs within your formula


--
Regards

Roger Govier


"BorisS" wrote in message
...
I need to copy/paste a sheet from one area to another sheet. I want to
mark
the columns as the letters where they were before movement. I can
make the
formula

="["&CHAR(64+COLUMN(Z1)-1)&"]"

to get up to column Z (the "-1" is because I've moved the columns over
by
one to the right...so the B column, I need to show as A, but this is a
minor
point). I cannot get this formula tweaked to where it will serve A-Z
AND AA
and beyond (since the CHAR does not recognize AA as the next logical
step).

Any help? Guessing it's an if/then, but not sure how.
--
Boris

formula for column letters
 

="[" & SUBSTITUTE( ADDRESS( 1, COLUMN( A1 ), 4 ), "1", "]" )

--
Regards,
Luc.

"Festina Lente"


"Roger Govier" wrote:

Hi Boris

=IF(COLUMN(A1)26,
CHAR(INT(COLUMN(A1)/26)+64)&CHAR(MOD(COLUMN(A1),26)+64),
CHAR(COLUMN(A1)+64))

will deal with column letters going beyond 26 (column Z)
Adapt to suit your needs within your formula


--
Regards

Roger Govier


"BorisS" wrote in message
...
I need to copy/paste a sheet from one area to another sheet. I want to
mark
the columns as the letters where they were before movement. I can
make the
formula

="["&CHAR(64+COLUMN(Z1)-1)&"]"

to get up to column Z (the "-1" is because I've moved the columns over
by
one to the right...so the B column, I need to show as A, but this is a
minor
point). I cannot get this formula tweaked to where it will serve A-Z
AND AA
and beyond (since the CHAR does not recognize AA as the next logical
step).

Any help? Guessing it's an if/then, but not sure how.
--
Boris