Automatically increment Sequence Number on Excel Sheet

Posted on 2007-07-31
Last Modified: 2010-05-18
Here is what I think is an interesting one. I "Weekly Update" sheet at work here. My boss would like the sheet to be numbered, i.e. Weekly update number 1, 2, etc. The problem is the weekly update gets run a few times a week! So I don't want it to increment every time I run it. Is there a way to set the increment so that it updates automatically once a week, say on Monday's at 9am? Sorry, I have a habit of not being clear, when I say we "run" the sheet I mean it is built basically from scratch by a macro each time we "run" it, so I'm looking for something to incorporate into the macro.
Question by:bpfsr
    LVL 92

    Accepted Solution

    In B1, I put the "original" date, 6/25/2007 9:00 AM.  In B2 I have the formula:

    Now, the "update number" will increment each Monday at 9 am.
    LVL 17

    Assisted Solution

    You don't need to run an update becuase you can calculate the week number, you just need to know the start week number.

    So in a cell you need something like:


    Where the value in the cell is the starting date of the sheet,  you could also get this date from properties.

    LVL 17

    Expert Comment

    There we go 2 similar answers, but both with the point that you do not need an update.  So it is up to your boss; but I suspect it would be better if week number is update at midnigh sunday, so the early birds on a monday don't get caught with a potential problem.

    But matthewspatrick's solution is slightly neater, if you have many sheets like this then perhaps we can find an improvment.
    LVL 17

    Expert Comment

    No I have tested matthewspatrick and there is a problem if the start date is not a monday.....

    Where as the extra maths in mine handles this issue correctly.

    LVL 17

    Expert Comment

    Another suggestion which could be cool in your needs if you go into the visual basic editor add a module and paste in the following code:

    ==================start of code
    Option Explicit
    Public Function CreationDate() As Date
    Static bDone As Boolean
    Static dtCreated As Date
    If Not bDone Then
        bDone = True
        dtCreated = Int(DateValue(Excel.ActiveWorkbook.BuiltinDocumentProperties("Creation date")))
    End If
    CreationDate = dtCreated

    End Function

    ===============end of code

    In this way you can then use the following code in any cell you like and whenever you open up the sheet the age in weeks will always show.

    LVL 80

    Assisted Solution

    Regardless of the formula that is used, you should consider whether people are apt to open an "old" Weekly Update. If so, then a formula will automatically update to a more recent number. To overcome this problem will require putting the formula in your VBA macro.

    'Returns 1 when macro is run any time the week of 6/25/2007. Adds 1 for each successive week.  _
           Pick a Monday for the DateValue. Results are returned to Sheet1 cell B1.
    Worksheets("Sheet1").Range("B1") = Int((Now - DateValue("6/25/2007"))/7) + 1
    Worksheets("Sheet1").Range("B1").NumberFormat = "#"
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now