Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in array | Excel Worksheet Functions | |||
Is it possible? | Excel Worksheet Functions | |||
Reusing formula | Excel Discussion (Misc queries) | |||
formula for changing CAPS to small letters | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |