Auto Lettering

Mollycat · External Since: Feb 12, 2009 Posts: 1

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

Gary''s Student · External Since: Oct 01, 2005 Posts: 3350

In A1, enter:

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

Pecoflyer · External Since: Feb 12, 2009 Posts: 1

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/showthread.php?t=63114

Glenn · External Since: Jan 12, 2009 Posts: 75

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.

Gary''s Student · External Since: Oct 01, 2005 Posts: 3350

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

Glenn · External Since: Jan 12, 2009 Posts: 75

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)