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.
 

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
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.
 
Master Excel Bundle

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.

Forum statistics

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

Which adblocker are you using?

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
Top