Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Number Change on Year (2003)

    Thanks again - that looks like just what I need

    Happy new year

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    Re: Number Change on Year (2003)

    Thanks Legare will try this also

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    473
    Thanks
    66
    Thanked 2 Times in 1 Post

    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. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Number Change on Year (2003)

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

    Steve

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    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
  •