Autofill A B C D...AA AB...

[cmendes]

Hello,

I remember that excel could complete a sequence is we gave it the first elements.
In this case I'm writing in three different cells (same column) A, B C and I want excel to do the rest as I drag it down: D E F and so on. But if do it I get A B C again...
Is there anything that i have to do??
I'm using Excel for mac 2011
Thanks in advance,
C

 

[basilafj]

Thank you you are the best,
but after ]Z9999 it is start giving ^A0001.

i uploaded the photo

 

[Rick Rothstein]

Maybe change the formula to this (untested but it should stop the codes at ZZ9999)...

=IF(OR(A1="ZZ9999",A1=""),"",CHAR(CODE(A1)+(RIGHT(A1,5)="Z9999"))&IF(RIGHT(A1,5)="Z9999","A",CHAR(CODE(MID(A1,2,1))+(RIGHT(A1,4)="9999")))&IF(RIGHT(A1,4)="9999","0001",TEXT(RIGHT(A1,4)+1,"0000")) )

 

[Tetra201]

Here is my take on this, with fewer function calls and fewer reference calls:

=IF(OR(A1={"","ZZ9999"}),"",SUBSTITUTE(ADDRESS(1,INT(RIGHT(A1,4)/9999)+SUM((CODE(MID(A1,{1,2},1))-64)*{26,1}),4,),1,"")&TEXT(MOD(RIGHT(A1,4),9999)+1,"0000"))

 

[basilafj]

Thank you dear for the support, i want to ask you.
is there is anyway to increase the number of cell more than 1048576 record per sheet?

 

[basilafj]

My dear friend, how to do that also in MS Access query?

 

[spamspambaconspam]

I know this is an old post, but I want to share a different method to create lists -- the difference is, you create the list once, and it gets permanently installed in to the lists that you can create with autofill.

Imagine using autofill to create:

• a list of all your employees
  • Barbara
  • Chuck
  • Dave...
• all the teams in your league
  • Bulldogs
  • Lions
  • Panthers
  • Tigers
  • Wolves...
• all the sizes of your metric and US Standard tools
  • 5/32
  • 4 mm
  • 3/16
  • 5 mm
  • 13/64
  • 7/32
  • 15/64
  • 6 mm...
• all the possible fields your league games might be played at...

Here's how:

1. Create your list.
   1. In any empty cell, enter one of the items in your list.
   2. Then, move to an adjacent cell (either down or right, it doesn't matter) and enter another item on your list.
   3. Continue until your list is created.
   4. Put your list in whatever order you prefer. (you don't need to order it, but know that autofill will put the items in the same order as your original setup, so if you want your co-workers in order by dept, or your league fields in order by how far away they are from your house, do that now)
   5. Select (highlight) the range of cells that comprise your list.
      
      
2. Add the range as a custom list.
   1. Go to File > Options > Advanced
   2. Scroll all the way down to almost the bottom, and click on "Edit Custom Lists"
      
      
      
      
      
      
   3. The highlighted range will already be entered in the "Import list from cell" box.
   4. Click "Import" and the list will be permanently added to your autofill lists.
      
      
   5. Click OK to finish.
      

Now, you can enter ANY item on your list in to a cell (it doesn't have to be the first item) and then drag the autofill handle - your list items will be automatically added, continuing the list from whatever item you start the drag from.

(This method may not provide the exact solution the original poster was looking for, but it's a handy way to create lists with autofill.)

HTH

~spammy