Help with formula to autonumber column

[Berko]

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.

=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.

 

[just_jon]

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]

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

=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]

I think one of these might work.

Book2
	A	B	C	D
1	Nr	Dt	Nr	Dt
2	1805	12/2/2005	1805	12/2/2005
3		dep for 12/2/2005		dep for 12/2/2005
4	1806		1806
5
Sheet1

 

[Berko]

I like the MAX() formula, but when I entered it in my IF() formula, it returned an error.

=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]

I like the MAX() formula, but when I entered it in my IF() formula, it returned an error.

=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]

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]

I like the MAX() formula, but when I entered it in my IF() formula, it returned an error.

=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]

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

 

[Berko]

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.