[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
9
Medium Priority
?
2,447 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
Comment
Question by:bpfsr
7 Comments
 
LVL 93

Accepted Solution

by:
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

by:inthedark
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

by:inthedark
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:inthedark
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

by:inthedark
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

by:byundt
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

by:Computer101
ID: 20343807
Forced accept.

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.

873 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