AUTO FILL

A

Avril M.

I need to create a list that runs from A through Z and then when that runs
out starts AA and so on (just the letters that run along the top of the
worksheet. I know I can type all these in via a custom list - but is there
an easier way? otherwise will take ages.
 
M

Max

Try this adaptation of a previous post by Glenn Schwandt in
..worksheet.functions (Mar 2001)

Put in any starting cell, say, B2:

=UPPER(IF(ROWS($A$1:A1)>18278,CHAR(96+INT(MOD(ROWS($A$1:A1)-18279,456976)/17
576)+1),"")&
IF(ROWS($A$1:A1)>702,CHAR(96+INT(MOD(ROWS($A$1:A1)-703,17576)/676)+1),"")&IF
(ROWS($A$1:A1)>26,
CHAR(96+INT(MOD(ROWS($A$1:A1)-27,676)/26)+1),"")&CHAR(97+MOD(ROWS($A$1:A1)-1
,26)))

Copy B2 down to B257 to fill: A, B, C ... IV

The formula can fill in the alpha series beyond IV to the extent of Excel's
65536 rows, viz. filling B2 to B65536 will return the alpha series: A, B, C
.... CRXO !

If filling across, just edit > replace :ROWS($A$1:A1)
with: COLUMNS($A$1:A1)
in the formula, viz. use in the starting cell:

=UPPER(IF(COLUMNS($A$1:A1)>18278,CHAR(96+INT(MOD(COLUMNS($A$1:A1)-18279,4569
76)/17576)+1),"")&
IF(COLUMNS($A$1:A1)>702,CHAR(96+INT(MOD(COLUMNS($A$1:A1)-703,17576)/676)+1),
"")&IF(COLUMNS($A$1:A1)>26,
CHAR(96+INT(MOD(COLUMNS($A$1:A1)-27,676)/26)+1),"")&CHAR(97+MOD(COLUMNS($A$1
:A1)-1,26)))
 
E

Excel_Geek

Assuming you'll start in cell A1 with "A", and make a column with this
list, here's a formula you could copy all the way down:

=LEFT(ADDRESS(1,ROW(B1),4,1),FIND("1",ADDRESS(1,ROW(B1),4,1))-1)
 
Top