M
Mollycat
How can I set up the first column to automatically letter each row as I go ?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
In A1, enter:
=MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) and copy down
=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 ?
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
Gary''s Student
Glenn your formula is great!
G
Glenn
Gary''s Student said: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)