# 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 Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### 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

ADVERTISEMENT

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

ADVERTISEMENT

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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,163,999
Messages
5,834,813
Members
430,323
Latest member
Regash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back