Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Janus
 
Posts: n/a
Default How can I increase succesive rows by alphabet rather than number?


I can easily create a column of numbers that increase in increments of 1
in each successive cell by adding 1 to the cell above. The first cell
is the only cell where I manually input a number. I want to set a
column so that I can input a letter, say E, in the top cell and the
cells below will fill in with F, G, H, etc. If this can be done, am I
limited to a maximum of 26 cells to match the number of letters in the
alphabet, or can I go into letters like AA, AB, AC, etc.


--
Janus
------------------------------------------------------------------------
Janus's Profile: http://www.excelforum.com/member.php...o&userid=26890
View this thread: http://www.excelforum.com/showthread...hreadid=477252

  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default How can I increase succesive rows by alphabet rather than number?

One way:

Create a list with A to Z in A1:A26. Select the cells. Choose
Tools/Options/Custom lists. Click Import.

This will only give you 26 cells without repetition. You could make a 52
item list from A to AZ, etc.



In article ,
Janus wrote:

I can easily create a column of numbers that increase in increments of 1
in each successive cell by adding 1 to the cell above. The first cell
is the only cell where I manually input a number. I want to set a
column so that I can input a letter, say E, in the top cell and the
cells below will fill in with F, G, H, etc. If this can be done, am I
limited to a maximum of 26 cells to match the number of letters in the
alphabet, or can I go into letters like AA, AB, AC, etc.

  #3   Report Post  
L. Howard Kittle
 
Posts: n/a
Default How can I increase succesive rows by alphabet rather than number?

Hi Janus

Perhaps by using this 3 ton formula I saved from Bernie Deitrick (12/22000).

=CHAR((A1-(A1-INT(A1/26)*26)-((A1-(A1-INT(A1/26)*26))/26-INT((A1-(A1-INT(A1/26)*26))/26/26)*26)*26)/26/26-INT((A1-(A1-INT(A1/26)*26)-((A1-(A1-INT(A1/26)*26))/26-INT((A1-(A1-INT(A1/26)*26))/26/26)*26)*26)/26/26/26)*26*26+97)&CHAR((A1-(A1-INT(A1/26)*26))/26-INT((A1-(A1-INT(A1/26)*26))/26/26)*26+97)&CHAR(A1-INT(A1/26)*26+97)

Copy - Paste in B1.
In C1 enter =RIGHT(B1,2)
In A1 enter a 0 (zero). B1 will show "aaa"
Select the three columns and pull down to no more than 677 rows.

Past that C column will begin to duplicate. If having 3 letters in your
lettering system is OK, then omit the C column and use the B column results.
ZZZ occurs at 17,576.

To get rid of the formulas do a copy and paste special values.

I can not answer ANY questions about this formula. I also made the mistake
of just printing it out instead of saving in Excel. Typing it back into
Excel was a virtual disaster. Hit enter and "... your formula is missing a
(..." 'bout 50 times it seemed! :)

A just as an aside, my initials LHK occur in row 7629.

HTH
Regards,
Howard

"Janus" wrote in
message ...

I can easily create a column of numbers that increase in increments of 1
in each successive cell by adding 1 to the cell above. The first cell
is the only cell where I manually input a number. I want to set a
column so that I can input a letter, say E, in the top cell and the
cells below will fill in with F, G, H, etc. If this can be done, am I
limited to a maximum of 26 cells to match the number of letters in the
alphabet, or can I go into letters like AA, AB, AC, etc.


--
Janus
------------------------------------------------------------------------
Janus's Profile:
http://www.excelforum.com/member.php...o&userid=26890
View this thread: http://www.excelforum.com/showthread...hreadid=477252



  #4   Report Post  
Janus
 
Posts: n/a
Default How can I increase succesive rows by alphabet rather than number?


Thanks, Guys. I tried JE McGimpsey's method and it works quite well. I
just saved the master list in a separate workbook and can now easily
import it wherever needed.

Howard, I don't understand that 3 ton formula any better than you, but
rather than type it I pasted it in, followed the directions, and it
also works. Someone with more time than me worked that one up.

Janus


--
Janus
------------------------------------------------------------------------
Janus's Profile: http://www.excelforum.com/member.php...o&userid=26890
View this thread: http://www.excelforum.com/showthread...hreadid=477252

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert a number of rows based on a value in a cell on active row iRocco Excel Discussion (Misc queries) 1 August 11th 05 06:18 AM
Insert a number of rows based on a value in a cell on active row iRocco Excel Worksheet Functions 0 August 10th 05 08:46 PM
Maximum number of rows? Allan Bach Excel Discussion (Misc queries) 5 April 14th 05 12:37 PM
Increase Number of Worksheets Hoochi Coochi Man Excel Discussion (Misc queries) 4 December 10th 04 05:17 PM
increasing the number of rows of a worksheet beyond 65536? ibu Excel Discussion (Misc queries) 2 November 26th 04 09:56 AM


All times are GMT +1. The time now is 01:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"