# Help with formula to autonumber column

#### Berko

##### Board Regular
I have a column of receipt numbers that I need to auto-increment. This wouldn't be hard except for every time a deposit is made, the receipt number cell is left blank. I thought I would use an IF(Left formula to account for that. Here is the formula I came up with, but it is entering in the cell as a string. I'm sure it's really something silly, but it eludes me.
Code:
``=IF(LEFT(Address(Row() - 1,Column()+1)),7) = "Deposit",=SUM(Indirect(Address(Row() - 2,Column())) + 1),=SUM(Indirect(Address(Row() - 1,Column()))+1))``
Thanks in advance to all you Excel geniuses.

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### just_jon

##### Legend
If you can give a sample of your worksheet, I think we can give you a simpler formula.

BTW, ii it the formula showing up as itself instead of producing a result? If it is, then the cell is formatted as Text; re-format as General, then reconfirm the formula w/ Enter.

BUT, I think you can use a much simpler formula.

#### Berko

##### Board Regular
I can't use the HTML Maker here at work, but here is some sample data.
A B
829 1805 12/2/2005
830 [blank] Deposit for 12/2/2005
831 1806 12/6/2005

Cell A832 needs to be 1807 and autonumbered down, skipping the cell in column A when the adjacent cell in column B indicates a Deposit. (Thus, the LEFT() portion of my formula.) I hope this is enough information to help. I think I also need a way to make the cell in column A adjacent to the Deposit information automatically go blank when the Deposit date is entered, which happens in a macro, as well as a way to have the cells in column A be blank below the last line, i.e. in A832, until I enter something in B832, I don't want anything to show. Something like
Code:
``=IF(B832 = "","",IF(LEFT(B831,7) = "Deposit",=A830 + 1,=A831 + 1))``
I don't know if that formula would work since I wrote it off the top of my head, but I think you get the idea.

#### just_jon

##### Legend
I think one of these might work.
Book2
ABCD
1NrDtNrDt
2180512/2/2005180512/2/2005
3dep for 12/2/2005dep for 12/2/2005
418061806
5
Sheet1

#### Berko

##### Board Regular

I like the MAX() formula, but when I entered it in my IF() formula, it returned an error.
Code:
``=IF(B6="","",IF(LEFT(B6,7)="Deposit","",=Max(A\$5:A5)+1))``
When I enter the Max portion by itself, it works great. Any idea why it won't work in the IF()?

#### just_jon

##### Legend
Berko said:
I like the MAX() formula, but when I entered it in my IF() formula, it returned an error.
Code:
``=IF(B6="","",IF(LEFT(B6,7)="Deposit","",=Max(A\$5:A5)+1))``
When I enter the Max portion by itself, it works great. Any idea why it won't work in the IF()?
What was the error?

#### Berko

##### Board Regular

It simply said there was an error in the formula. It didn't give any indication what the error was. When I ESC out of the cell, it restores my previous, less elegant, but more or less effective formula.

#### Ronald Moore

##### Board Regular
Berko said:
I like the MAX() formula, but when I entered it in my IF() formula, it returned an error.
Code:
``=IF(B6="","",IF(LEFT(B6,7)="Deposit","",=Max(A\$5:A5)+1))``
When I enter the Max portion by itself, it works great. Any idea why it won't work in the IF()?

I believe you just need to delete the = before Max:

=IF(B6="","",IF(LEFT(B6,7)="Deposit","",Max(A\$5:A5)+1))

#### Berko

##### Board Regular
Thanks. I will try that when I go back to work tomorrow.

#### Berko

##### Board Regular
OK. Ronald, your code worked for a while, but when I created a new sheet for the new year, it didn't work anymore. It worked fine until I left a cell blank. So, 1000, 1001, 1002, 1003, 1004 entered brilliantly, but I left the receipt number blank when I entered the deposit record, and where the formula should have entered 1005 (the max of all the above cells plus 1), it entered 1001. Any idea why this is?

A B
1
2
3
4
5 1000
6 1001
7 1002
8 1003
9 1004
10 Deposit for 01/10/2006.
11 1001

A11 should be 1005. A5 is a static starting value for our receipt numbers for the year. A6 and down includes the formula Ronald posted. I hope this is enough information.

Replies
4
Views
173
Replies
1
Views
128
Replies
6
Views
57
Replies
6
Views
114
Replies
8
Views
369