Solved

Automatically increment Sequence Number on Excel Sheet

Posted on 2007-07-31
2,412 Views
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.
0
Question by:bpfsr
7 Comments

LVL 92

Accepted Solution

In B1, I put the "original" date, 6/25/2007 9:00 AM.  In B2 I have the formula:
=INT((NOW()-B1)/7)

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

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:

=INT((TODAY()-2)/7)-INT((DATEVALUE("31/7/2007")-2)/7)+1

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

0

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.
0

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.

0

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.

=INT((TODAY()-2)/7)-INT((CreationDate()-2)/7)+1
0

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 = "#"
0

LVL 1

Expert Comment

Forced accept.

Computer101
EE Admin
0

Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

Featured Post

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.

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!