Autoincrementing cell in Template

I am currently creating a template.  My final stumbling block is creating 
an auto incrementing cell in the template.  What I want to happen is that 
every time the template is opened, the next sequential number fills in the 
cell.  The format I am looking for is basically yy-#####.  For example, 
the first time it is used this year, it would assign the cell to 04-00001, 
followed by 04-00002, etc.  I am lost on how to do this.  Any ideas?

Thanks,
Andy

-- 
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
0
Andy
4/17/2004 3:25:16 PM
excel 39879 articles. 2 followers. Follow

1 Replies
208 Views

Similar Articles

[PageSpeed] 34

Hi
one way (using the Windows registry for storing the last number). Put
the following code in the workbook module (not in a standard module) of
your template:
- It changes cell A1 of the first sheet
- you may change the key identifiert according to your needs (e.g.
DEFAULTSTART, MYLOCATION, etc.)

    Private Sub Workbook_Open()
        Const DEFAULTSTART As Integer = 1
        Const MYAPPLICATION As String = "Excel"
        Const MYSECTION As String = "myInvoice"
        Const MYKEY As String = "myInvoiceKey"
        Const MYLOCATION As String = "A1"
        Dim regValue As Long

        With ThisWorkbook.Sheets(1).Range(MYLOCATION)
            If .Text <> "" Then Exit Sub
            regValue = GetSetting(MYAPPLICATION, MYSECTION, _
                    MYKEY, DEFAULTSTART)
            .Value = Format(date,"YY") & "-" & format(regValue,"00000")
            SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
        End With
    End Sub


--
Regards
Frank Kabel
Frankfurt, Germany


Andy Rabin wrote:
> I am currently creating a template.  My final stumbling block is
> creating an auto incrementing cell in the template.  What I want to
> happen is that every time the template is opened, the next sequential
> number fills in the cell.  The format I am looking for is basically
> yy-#####.  For example, the first time it is used this year, it would
> assign the cell to 04-00001, followed by 04-00002, etc.  I am lost on
> how to do this.  Any ideas?
>
> Thanks,
> Andy

0
frank.kabel (11126)
4/17/2004 3:32:10 PM
Reply:

Similar Artilces:

End Cells
I am trying to reduce the file size of a file I am creating. It is currently at 3.6MB, with about 10 worksheets. I posted earlier and someone recommended hitting Ctrl-End on my sheets to see where it was that Excel thought the end of the data was. I did this with a few of the workbooks and the end appears to be out in the middle of nowhere. They then recommended that I delete all the columns and rows past my actual data and then save the file. I tried this but when I hit Ctrl-End on those sheets it still takes me way past my data ranges. Any help would be appreciated. Thanks i...

Cell to return null instead of 0
I have a formula that returns 0 instead of null. When I sort the column in assending order the cells with 0 show up first. I need the cells to be null so they do not show up first in the sort. You can't have it both ways if the reason your formula returns zero instead of blank is because you would get an error if you use "" instead of 0, if not just change the formula that returns zero -- Regards, Peo Sjoblom (No private emails please) "dford" <dford@discussions.microsoft.com> wrote in message news:09BE2C7D-65BE-4815-BC79-2420CC22C631@microsoft.com... ...

WORD 2007 Template with VBA
Hi Folks, I have a template that I use to capture input from users who are requesting a scan. That template worked fine under 2003 and I was able to get it to run in 2007. However, when I tried to recreate the functionality starting from scratch in WORD 2007 I had problems. I have some VBA code that I use to interrogate an ancillary file which contains a number. I increment that number by one and place it into the Word document. Then I used content controls to prompt the user for information. When I "Open" the .dotm file all of the content controls and legacy c...

End/Beginning of Cell Search/Replace?
I find myself having to export data to word so I can do simple find/replace which I'd do in Word with ^p such as Find ^p(space key) Replace ^p (removes leading spaces) Find s^p Replace ^p (removes trailing s i.e. plurals) Is there anyway to do this with Excel S/R that is indicate beginning/end of cell? Hi, To search and replace with excel press CTRL + H if this helps please click yes thanks "msnyc07" wrote: > I find myself having to export data to word so I can do simple find/replace > which I'd do in Word with ^p such as > &g...

Sum Function Visible Cells only
if column a has the number 1, column b has the number 1 and column c has the number 1, column d has the function sum(A1:C1) for a total of 3. how can a change the formula so that I get an answer of 2 when column b is hidden. Microsoft has guessed your need: How to Use a VBA Macro to Sum Only Visible Cells found at http://support.microsoft.com/kb/150363 best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Brian" <Brian@discussions.microsoft.com> wrote in message news:390AA6D5-5938-4172-B8B4-7CBD78D9059E@microsoft.com... >...

Adding XY days to date in cells
Hi Excel gurus, Does anybody know, how to add for example 70 days to date in cell an then use it in condition ? I have column with date in every cell. I need the date go to red color when I add to date 70 days and the date go over specific date. Example - I have some date : 1.1.2005, now, if I add 70 days and th datum go over specific date, for example 1.2.2005, change color t red. I don't know, how to add days to date, and I don't know, how exactl build up a condition. Thanks in advance Satuch -- satuch ----------------------------------------------------------------------- satuc...

Cell Change Color
Hi All, I wonder if anyone can help me on this. I'm working on something which will help me to monitor inputs. e.g. A system will send a heartbeat every minute which I will be able capture as input to my excel spresheet, say cell C3. Now, this are the requirement for the output: If cell C3 is updating every minute consistently then cell A1 will remain or change to GREEN. If cell C3 is not updating after 1minute 30seconds then cell A1 will change to AMBER but if it is updating again cell A1 will change to GREEN again. If cell C3 is not updating after 2minutes the cell A1 will change t...

Return a value if criteria contained in cell
I would like to return a value if criteria is met. If A2 contains the word "apples", then in cell B2, write "apples". If A2 contains the word "oranges, then in cell B2, write "oranges". If A2 does not contain the words "apples" or "oranges", leave the cell blank. Can you help me with a formula for this? Thanks in advance. IF(OR(A2="apples",A2="oranges"),A2,"") "jhicsupt" wrote: > I would like to return a value if criteria is met. > > If A2 contains the word "apples", then in...

Inserting Text from a Cell into Formula
Hi All, Here's a doosie. Is there anyway to take the text value of a cell an insert that into a formula? But the twist is this, the text value i the cell represents the name of the worksheet. I am using Excel 2002. example > A2 is a list that contacts "1 Hour","2 Hours","3 Hours" etc all th > way up to "8 Hours". Then B2 contains the formula: ='A2'!A5. Th > formula is to assume that a value is selected for A2. Let's say th > value is "1 Hour", so the actual formula should be ='1Hour'!A5 Hope I'm m...

Template Wizard Add In
I am trying to send information from a template to a database I have set up. We set up a new template and database every month by copying and pasting the last template and database. It has worked in the past but now the template keeps looking for the last months file even when I update the database to the current month in the template wizard setup. Any ideas? Thanks, Jamie ...

Extracting Text from a Cell
I have a column in excell that has 1-4 names in each cell. I would like to extract the names into 4 seperate cells (column). The names are seperated by a comma and a soft return. I'd appreciate any help. Look at Data>Text To Columns -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Tony" <anonymous@discussions.microsoft.com> wrote in message news:2031101c4588f$bfe1f240$a001280a@phx.gbl... > I have a column in excell that has 1-4 names in each cell. > I would like to extra...

offset.cell
Hello, I need help designing a VBA Macro: I have in cell A1 "Paid" in cell G8 "total" in cell H8 $100.00 What I need is: to cell.find "paid" then to do cell.offset to G8 to select.copy the value in H8. Is this possible ? Regards, Minimally, the code looks like this: Sub Copy_If_Paid() If ActiveCell.Value = "Paid" Then ActiveCell.Offset(0, 7).Copy End If End Sub This works when the cell pointer is already on cell A1. A lot depends on how your information is displayed on the spreadsheet. Do you have many rows of Paid / Unpaid entries? Often I&#...

concantenatring 2 cells with with a line break
I have info in 2 cells. I want to add the info from one cell into the other cell but have the info from each cell show up on different lines within the cell In a third cell, put =A1&" "&A2 and format that cell as "Wrap text" Vaya con Dios, Chuck, CABGx3 "dick" <dick@discussions.microsoft.com> wrote in message news:C915AAB6-9B36-45D3-924B-963A2E8DBB6A@microsoft.com... > I have info in 2 cells. I want to add the info from one cell into the other > cell but have the info from each cell show up on different lines within the > cell W...

text does not wrap for whole cell in Excel
When using Excel, and if the size of the cell is the maximum size in height, I notice that when entering text, it stops wrapping text after a while. This is very frustrating, because when i need to print my spreadsheet, I do not see all my test. Instead, i have to use ALT+Enter to manually shift text to the next line. This is very frustrating. Any way to fix this? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see ...

email templates 01-09-05
Two questions. 1. Can you format a Date field. Example: Insert the contacts birthday field in a template. It shows the datetime in datetime format. Useless 2. When users use the template that was created. It seems the Subject is not inserted in Subject line of the email. Thanks, Jaime Hi Jaime, I have the same problem with the title that doesn't appear when using a email template. Please publish the answer if you found something. Christophe "Jaime" wrote: > Two questions. > > 1. Can you format a Date field. Example: Insert the contacts birthday ...

Border Formatting Disappearing when data entered into cell
I have an Excel Worksheet that is formatted with borders and shading. When I type into a cell, or paste into a cell, the formatting disappears, and seems to revert to some default value. Can anyone help explain how to make Excel act like iot's supposed to? ...

Does Excel 2003 have a shortcut command to wrap text in a cell?
When cells in a spreadsheet are populated with text, is there a shortcut command that can be added to the toolbar for 'wrap text'? Also, when there is a long text string entered in a cell, it appears across multiple [blank] cells in that row automatically. If this is a formatting feature, how/where can I access this option? There is no specific button for that command, but you can either add the Cells... button or create a simple that can be attached to a custom button. The long text issue, I believe, is normally considered a feature that allows longer labels to be fully dis...

Combobox value populate cell selection
I am looking for button code to have a selected cell range merged and populated with value chosen from combobox. This value is centered in the merged cell selection. The cells range is defined manually with mouse. Bart Have a look at this event code which you can refine. No error-checking for data in the selected range...........assumes the mergerange is empty when selected. Runs when a value is selected from Combobox1 Private Sub ComboBox1_Change() Set srng = Application.InputBox(prompt:= _ "Select A Range", Type:=8) With srng .HorizontalAlignment = xlCen...

Typing in Cells
How do you make the cells remember - Like I start to type 'Phoenix' and the word just pops in like it remembered the word. Is this random or does it have to see a pattern? "jeanngoodyear" <jeanngoodyear@discussions.microsoft.com> wrote: > How do you make the cells remember - Like I start to type 'Phoenix' and > the > word just pops in like it remembered the word. Is this random or does it > have to see a pattern? In Excel 2003, this is controlled by the AutoComplete option under Tools > Options > Edit. Not random. Cont...

Insert PDF into Excel Cell?
I would like to do a MS Word mail merge using excel as the data source for a fax blast. However, I need to send a specific page to a specific fax number. I was thinking of setting up an excel document with [name, fax number, document] columns as the data source, and then merge with my word document. My concern is that each recipient get the correct (individualized) page, along with the cover sheet. Is is possible to embed a file (pdf or word) into an excel cell? That way, i could use the excel file to set up the merge (Name1, Fax Number1, Document 1; Name2, Fax Number,2 Document 2; Name3,...

Make a cell blank given a code
I am trying to achieve a blank cell which currently has a formula relating to a different sheet. On sheet named "week" In cell M32 I have the following code =IF(OR(D33>0,G33>0),'Stats-Job'!M9,) However if the code M is in another cell, at say H32, then I want the cell M32 to remain blank, Any help appreciated. Mel just wrap in another if =if(h32="M","", yourformula) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mel" <mgsg@bigpond.net.au> wrote in message news:ukfmit1oGHA.1600@TK2MSFTNGP04.ph...

how to work with non adjacent cells in Excel
What would you be doing with the non-adjacent cells? I could sum like =A1 + G1 Or any number of similar functions. Gord Dibben MS Excel MVP On Wed, 18 Jun 2008 09:36:00 -0700, weath_erhead <weatherhead@discussions.microsoft.com> wrote: ...

How do I tell Excel to insert data from a cell 4 to the left?
Hi there, Here's the situation. I have a bunch of game prices in cells I8:I15. In cells B8:B15 are the names of these games. I have a cell that gives me the highest price [=MAX(I8:I15)] but instead of showing the price, I want it to show the name of the game. But I don't want to have to enter the name myself, I want Excel to do this automatically so if the prices change it can update itself. Is this possible? [Or does it even make any sense? ;)] Thanks in advance for any help (: Cheers, Jake. Try this... =INDEX(B8:B15,MATCH(MAX(I8:I15),I8:I15,0)) Note that i...

Trying to create conditional format cell
trying to automate a form. Have 1st part of what I want to do but don't know how to do the rest. Example Cell "C" = Cell A minus cell B . Now what I want to do is this: if Cell C is 10 percent less than Cell D then text is Red, Bold, Fill is yellow. is this Possible? Not very good at math functions. select cell C2. Format - Conditional Format. Change first box to "Formula is". In second box, input this: =C2<0.9*D2 Click the format button, on text tab, select bold, and on pattern tab, choose yellow. Ok out. (assuming you meant 10% less or more) -- ...

Link to specific cells on Excel Spreadsheet to generate reports
Is it possible to link to a specific cell in an excel spreadsheet in a report? Like bind text boxes to specific cells on an Excel spreadsheet? ...