Help with formula to autonumber column

Berko

Board Regular
Joined
Oct 13, 2005
Messages
64
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.
 

Some videos you may like

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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Oct 13, 2005
Messages
64
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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Oct 13, 2005
Messages
64

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
Joined
Sep 3, 2002
Messages
10,473
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
Joined
Oct 13, 2005
Messages
64

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
Joined
Aug 22, 2005
Messages
101
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
Joined
Oct 13, 2005
Messages
64
Thanks. I will try that when I go back to work tomorrow.
 

Berko

Board Regular
Joined
Oct 13, 2005
Messages
64
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,094
Members
412,441
Latest member
kelethymos
Top