Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    73

    Question Unanswered: Auto Number Formula?

    I have a spreadsheet that needs to be numbered automatically. However, I need it numbered based on cells in the next column. For instance:

    1 Apples
    1 Apples
    1 Apples
    2 Oranges
    2 Oranges
    3 Pears
    3 Pears
    3 Pears
    3 Pears
    4 Plums
    5 Mangos

    I know it's an IF statement but I can't see the wood for the trees.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    if(B1 = B2, A1, A1 + 1)
    Does that do what you want?
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Posts
    73
    It doesnt work. I get a circular reference error. Thank you for trying.
    Last edited by X-Centric; 04-30-07 at 00:17.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    colA | colB
    1 | Apples
    2 | Apples B2 = B1
    3 | Apples B3 = B2
    4 | Oranges B4 <> B3 +1
    5 | Oranges B5 = B4
    6 | Pears B6 <> B5 +1
    7 | Pears B7 = B6
    8 | Pears B8 = B7
    9 | Pears B9 = B8
    10 | Plums B10 <> B9 +1
    11 | Mangos B11 <> B10 +1
    Code:
    if(B2 = B1, A1, A1 + 1)
    Try the above, small change + the logic behind my suggestion
    George
    Home | Blog

  5. #5
    Join Date
    May 2004
    Posts
    73
    It worked. Thank you for you help. I appreciate it very much.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You're very welcome.
    I would like to point out that this will only be "accurate" if your column is sorted appropriately.
    1 Apples
    1 Apples
    1 Apples
    2 Oranges
    2 Oranges
    3 Pears
    3 Pears
    3 Pears
    3 Pears
    4 Plums
    5 Mangos
    6 Apples
    7 Pears
    George
    Home | Blog

  7. #7
    Join Date
    May 2004
    Posts
    73
    Got it.

Posting Permissions

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