Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a number of rows based on a value in a cell on active row | Excel Discussion (Misc queries) | |||
Insert a number of rows based on a value in a cell on active row | Excel Worksheet Functions | |||
Maximum number of rows? | Excel Discussion (Misc queries) | |||
Increase Number of Worksheets | Excel Discussion (Misc queries) | |||
increasing the number of rows of a worksheet beyond 65536? | Excel Discussion (Misc queries) |