Auto Lettering

P

Pecoflyer

Mollycat;227036 said:
How can I set up the first column to automatically letter each row as
go ?
Click to expand...

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
 
G

Gary''s Student

In A1, enter:

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

Glenn

Mollycat said:
How can I set up the first column to automatically letter each row as I go ?
Click to expand...


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.
 
G

Glenn

Gary''s Student said:
Glenn your formula is great!
Click to expand...


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)
 
Top