Auto Lettering
 

How can I set up the first column to automatically letter each row as I go ?

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

Auto Lettering
 

In A1, enter:

=MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) and copy down
--
Gary''s Student - gsnu200833

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.

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.

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)