Auto Lettering

[Mollycat]

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

 

[Pecoflyer]

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

Which letter group should be use after the "z"

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste

 

[Gary''s Student]

In A1, enter:

=MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) and copy down

 

[Glenn]

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.

 

[Gary''s Student]

Glenn your formula is great!

 

[Glenn]

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)