# Thread: Auto Number Formula?

1. Registered User
Join Date
May 2004
Posts
73

## 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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Code:
`if(B1 = B2, A1, A1 + 1)`
Does that do what you want?

3. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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

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

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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

7. Registered User
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
•