Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Lettering
How can I set up the first column to automatically letter each row as I go ?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Lettering
Mollycat;227036 Wrote: How can I set up the first column to automatically letter each row as I go ? Which letter group should be use after the "z" ? -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=63114 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Lettering
In A1, enter:
=MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) and copy down -- Gary''s Student - gsnu200833 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Lettering
Mollycat wrote:
How can I set up the first column to automatically letter each row as I go ? And to extend this beyond the capabilities of the solution provided by "Gary''s Student"... =MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) ....which is limited to 256 rows: =IF(ROW()18277,CHAR(64+MOD(INT((ROW()-18279)/17576),26)+1),"")& IF(ROW()702,CHAR(64+MOD(INT((ROW()-703)/676),26)+1),"")& IF(ROW()26,CHAR(64+MOD(INT((ROW()-27)/26),26)+1),"")& CHAR(64+MOD(ROW()-1,26)+1) This should be good for close to 700,000 rows. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Lettering
Glenn your formula is great!
-- Gary''s Student - gsnu200832 "Glenn" wrote: Mollycat wrote: How can I set up the first column to automatically letter each row as I go ? And to extend this beyond the capabilities of the solution provided by "Gary''s Student"... =MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) ....which is limited to 256 rows: =IF(ROW()18277,CHAR(64+MOD(INT((ROW()-18279)/17576),26)+1),"")& IF(ROW()702,CHAR(64+MOD(INT((ROW()-703)/676),26)+1),"")& IF(ROW()26,CHAR(64+MOD(INT((ROW()-27)/26),26)+1),"")& CHAR(64+MOD(ROW()-1,26)+1) This should be good for close to 700,000 rows. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Lettering
Gary''s Student wrote:
Glenn your formula is great! Thanks, but I may have overestimated it's effectiveness...probably closer to 475,000 rows. Which means in Excel 2007 you would need to add another layer to cover the whole possible column: =IF(ROW()932230,"B",IF(ROW()475254,"A",""))& IF(ROW()18277,CHAR(64+MOD(INT((ROW()-18279)/17576),26)+1),"")& IF(ROW()702,CHAR(64+MOD(INT((ROW()-703)/676),26)+1),"")& IF(ROW()26,CHAR(64+MOD(INT((ROW()-27)/26),26)+1),"")& CHAR(64+MOD(ROW()-1,26)+1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I Reset Column lettering | Excel Worksheet Functions | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
Tool Bar lettering problems | Excel Discussion (Misc queries) | |||
How to AUTO SAVE as opposed to turning on auto recovery: EXCEL | Excel Discussion (Misc queries) | |||
excel links update not working in auto, calculations in auto | Excel Worksheet Functions |