Document numnbering system - restart count every year

[anderson7777]

Hello there,

I am a bit stuck and despite my best attempts to find a solution I have ended up with loads of IF statements that are nowhere near what I need.

I am working on a document numbering system which allocates a new number once the document owner (B column), document title (C column) and date (D column) have been inputed. Such documents from 2014 would have something like this: D 14 1, D 14 2, D 14 3, etc. "D" sits in Column E, the year sits in Column F and the reference number sits in column G.

The problem arises when the year changes. I have managed to get the year right (column F) using this statement: =IF(AND(B9>0,C9>0,D9>0), RIGHT(YEAR(D9), 2), ""). However I am struggling to make the reference number start from 1 in 2015 and then all over again in 2016 and so on.

the second part of my problem is that 2014 already reached D 14 7 which means that, for the remainder of the year, it has to continue from D 14 8 and, as explained above, the first document in 2015 would become D 15 1.

I have tried to autofill columns next to the main table to use as reference and I got as far as this (I apologize if this is truly ridiculous but it goes to show that I really want to fix this):

=IF(F8=$H$8,J10,IF(or(F8=$H$9,F8=$H$10,F8=$H$11,F8=$H$12,F8=$H$13,F8=$H$14,F8=$H$15,F8=$H$16,F8=$H$17,F8=$H$18,F8=$H$19,F8=$H$20,F8=$H$21,F8=$H$22,F8=$H$23,F8=$H$25,F8=$H$26,F8=$H$27,F8=$H$28,F8=$H$29,F8=$H$30,F8=$H$31,F8=$H$32,F8=$H$33,F8=$H$34,F8=$H$35,F8=$H$36,F8=$H$37,F8=$H$38,F8=$H$39,F8=$H$40,F8=$H$41,F8=$H$42,F8=$H$43,F8=$H$44),k10,""))

where the cells in column H are 14, 15, 16, ... , 50; J10 is the next number for 14; K10 is an simple autofill list starting with 1. As confident as I was that this would work, all it gives me is a blank cell (the value_if_false from the second IF)

The whole point of this exercise is to stop using the current document numbering system (involves Lotus Notes) as it will be very difficult to receive support for it going forward. Also, excel, in theory, it sounds so obvious that it can do what I need, I just don't know what to do. The reason I went all the way to 50 (2050), as weird as it sounds, I want to leave no room for error in the future; once it works, I will lock those cells and it will work forever.

Any advice is welcomed. Because I am a beginner, I am hoping that I can avoid VBA as I have already tried a few VBA workarounds suggested around this forum, to no avail.

Many thanks

 

[steve the fish]

Something like this? Works once one entry has been made.

=IF(F2=F3,G2+1,1)

 

[ChrisM]

Maybe I'm oversimplifying this, but I don't see why you've tried to use all these long formulas. You hardcode the very first document number "1", then everything after that should be a simple formula:

Check this row's year vs last year's row: If the same, add 1, if different, set equal to 1

Assume your first document is in row 1, and columns E,F,G contain "D", year, and Ref # . As I mentioned, hardcode this first ref number.

now in row 2 the formula for reference number is:

=If(F2<>F1, 1, G1+1)

 

[anderson7777]

Re: Document numbering system - restart count every year

Maybe I'm oversimplifying this, but I don't see why you've tried to use all these long formulas. You hardcode the very first document number "1", then everything after that should be a simple formula:

Check this row's year vs last year's row: If the same, add 1, if different, set equal to 1

Assume your first document is in row 1, and columns E,F,G contain "D", year, and Ref # . As I mentioned, hardcode this first ref number.

now in row 2 the formula for reference number is:

=If(F2<>F1, 1, G1+1)

ChrisM...you are an absolute genius, thank you. I cannot believe how far off the reservation I was with my so called workarounds. You are not oversimplifying, I was overcomplicating this, which led me to the mess you saw in my first post. Much appreciated