Results 1 to 7 of 7
  1. #1
    mapfax
    Guest

    Alphanumeric Incrementing

    Hello everybody I'm a new member [img]/forums/images/smilies/wink.gif[/img]

    I am trying in vain to get this to work but I'm going round in circles (indeed I'm even getting circular references).

    I am trying to automate an alphanumeric sequence for each new row entered - L1, L2, (might then change to) L2a, L2b, L2c, (then change back to) L3, L4, L4a, etc.

    This is one spreadsheet of many (1000 rows a month) that will be consolidated via import to an overall master (none of which are networked). The other spreadsheets would start with differenct letters (A,F,H,M,S) eg A1,A2,etc.

    I suppose what I am trying to achieve is rather like automated MS Word Numbered lists.

    Anybody any ideas ?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Alphanumeric Incrementing

    You could try something like this :

    ="L"&ROW()-X

    Where X is the number of the first row minus 1. If you enter the above formula in A3, it would read = "L"&Row()-2. (3rd row minus 1).
    If you wanted it formatted like L001, you could use = "L"&TEXT((ROW()-X),"000") - change the number of zeros to suit the format you would like.

    See if that suits your requirements, and welcome to the Lounge.

    Andrew C

  3. #3
    mapfax
    Guest

    Re: Alphanumeric Incrementing

    Thanks Andrew, but it doesn't quite do what I need. An example would look like this:

    +-----A-------B
    1-----L1------comment from person 1
    2-----L2------comment from person 2
    3-----L2A-----another comment from person 2
    4-----L2B-----another comment from person 2
    5-----L2C-----another comment from person 2
    6-----L3------comment from person 3
    7-----L4------comment from person 4
    8-----L5------comment from person 5
    9-----L5A-----another comment from person 5
    10----L6------comment from person 6

    I hope that makes it a little clearer. Thanks anyway.
    What I hope to do is automate this as each row is entered but with the ability to change to a sub number then back to the main number as required.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Alphanumeric Incrementing

    Hi, and sorry for not reading your first post more closely, as it was clear enough. The problem is there is no automatic way of knowing that a particular response is the first, second etc from any one person. Auto numbering in Word (as far as I know) is dependent on the use of tabs or indents, which equate to columns in Excel. So perhaps with the inclusion of additional columns and the use of a convention might help. The convention being that the 1st person is numbered 1 and their responses labelled a, b, c etc. 2 columns can be used for numbering and a third added as a composite if required. So in A2 you enter 1 and in column B2 you enter =IF(A2=A1,CHAR(CODE(B1)+1),"a"), and in C2 enter ="L"&A2&B2. This should give auto numbering except in column A, where you need to enter the number of the person, as that may or may not be the same as the previous entry.

    Hope you can follow it.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Alphanumeric Incrementing

    How can it be determined when the person making the comment changes?
    Legare Coleman

  6. #6
    mapfax
    Guest

    Re: Alphanumeric Incrementing

    Andrew thanks for this. I think I will be able to make this work. It is much more simpler than the other avenues I have been going down. Thanks again.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Alphanumeric Incrementing

    Let's hope none of your respondents are too verbose, because as it stands after the 26th comment(z) it might start to go wrong.

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •