[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Automatically increment Sequence Number on Excel Sheet

Posted on 2007-07-31
Medium Priority
2,447 Views
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

LVL 93

Accepted Solution

Patrick Matthews earned 672 total points
ID: 19601663
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

inthedark earned 664 total points
ID: 19601748
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

ID: 19601793
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

ID: 19601882
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

ID: 19602287
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 81

Assisted Solution

byundt earned 664 total points
ID: 19602290
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

ID: 20343807
Forced accept.

Computer101
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month19 days, 21 hours left to enroll