Free Microsoft Office Help for all Applications! |
|
|
|
|||||||
| Register | Donate |
Code Uploads/ Downloads |
FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
| Worksheet Functions Post questions in this forum if they are related to using Microsoft Excel Worksheet Functions, e.g Forumale or built in functionality etc. |
|
|
|
LinkBack | Thread Tools | Display Modes |
12th February 2009, 03:02 PM
|
|||
|
|||
Auto Lettering
------ Register to get rid of these "In Post" ads! ------
How can I set up the first column to automatically letter each row as I go ? 
Did you find this post helpful?  |
|
| The Code Cage Advertisment | ||
| ||
|
To stop seeing these ads and get other benefits check This page! |
|
12th February 2009, 03:04 PM
|
||||
|
||||
|
Re: Auto Lettering
------ Register to get rid of these "In Post" ads! ------
Quote:
__________________
Cheers -
Did you find this post helpful? |
|
|
12th February 2009, 03:38 PM
|
|||
|
|||
|
RE: Auto Lettering
------ Register to get rid of these "In Post" ads! ------
In A1, enter: =MID(ADDRESS(1,ROW()),2,LEN(ADDRESS(1,ROW()))-3) and copy down -- Gary''s Student - gsnu200833
Did you find this post helpful? |
|
| The Code Cage Advertisment | ||
| ||
|
To stop seeing these ads and get other benefits check This page! |
|
12th February 2009, 06:53 PM
|
|||
|
|||
|
Re: Auto Lettering
------ Register to get rid of these "In Post" ads! ------
Mollycat wrote: > 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.
Did you find this post helpful? |
|
|
12th February 2009, 09:34 PM
|
|||
|
|||
|
Re: Auto Lettering
------ Register to get rid of these "In Post" ads! ------
Glenn your formula is great! -- Gary''s Student - gsnu200832 "Glenn" wrote: > Mollycat wrote: > > 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. >
Did you find this post helpful? |
|
|
12th February 2009, 10:24 PM
|
|||
|
|||
|
Re: Auto Lettering
------ Register to get rid of these "In Post" ads! ------
Gary''s Student wrote: > 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)
Did you find this post helpful? |
|
| The Code Cage Advertisment | ||
| ||
|
To stop seeing these ads and get other benefits check This page! |
|
| Bookmarks |
| Tags |
| auto, lettering |
New topics in Worksheet Functions
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | |
| Display Modes | |
|
|
|
The Code Cage Affilliates
|
Linear Mode
