# Thread: Number Change on Year (2003)

1. ## Number Change on Year (2003)

Hi Loungers,

I hope someone can help. I want to be able to recalculate a number based on the year and restart the numbering with the year as an extension - I hope that makes sence.

The attached sample will provide an idea of what I'm after

2. ## Re: Number Change on Year (2003)

If you create an auxiliary column with the year, you can use this to restart the numbering. In the attached version, F1 contains =YEAR(C1), and G1 contains =COUNTIF(\$F\$1:F1,F1)&" - "&RIGHT(F1,2). Both formulas are filled down as far as needed. You can hide the intermediate column if you don't want to see it.

3. ## Re: Number Change on Year (2003)

Thanks again - that looks like just what I need

Happy new year

4. ## Re: Number Change on Year (2003)

If you don't want to add the extra column in Hans solution, then the formula below entered into A2 and filled down as far as necessary will also work. See the attached workbook.

<pre>=IF(AND(B2<>"",C2<>""),IF(YEAR(C1)=YEAR(C2),T EXT(VALUE(LEFT(A1))+1,"0")&" - "&TEXT(C2,"yy"),"1 - "&TEXT(YEAR(C2),"yy")),"")
</pre>

5. ## Re: Number Change on Year (2003)

Thanks Legare will try this also

6. ## Re: Number Change on Year (2003)

Han & Legare,

Thankyou both for your suggestions, they both work Ok - however, in testing your suggestions I notice that numbers can be duplicated, ie two 1.05 if year go 2005, 2004, 2005. The number generated is used as a unique reference number so duplicate numbers cannot occur.

Any thoughts?

7. ## Re: Number Change on Year (2003)

I think that the years are going to have to be together to do what you want with a formula. If you can't sort on the date column, then you would have to use a macro. The best I could think of would be a Worksheet Change event routine. However, this macro would have to recalculate and replace ALL of the sequence numbers every time any cell in the date column changed. This would cause the sheet to be VERY slow if there were more than a few rows.

8. ## Re: Number Change on Year (2003)

I don't think Hans' formula will give you duplicates.

Steve

9. ## Re: Number Change on Year (2003)

As Steve notes, my formula won't generate duplicate numbers even if the dates are not in chronological order. The formula counts the number of cells with the same year in the rows up to and including the current row. If there are cells with another year in between, they will not be counted.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•