New Contributor

# Formula Help

Hi -

I'm not sure if this formula even exists and when I try and type something into Google to help me find out, I'm not coming up with much.

I use an Excel template to create quotes for my customers and one of the cells has that customer's unique quote number in it.  Each completed quote is then saved with that unique quote number as an Excel spreadsheet so that I can easily find the saved documents by quote number.

Is there a way to create a formula that essentially counts up that quote number each time I open the template?

For example: if I completed and saved a quote number 1589 as an Excel Template, I would have typed "1589" in the cell for "Quote Number" and saved my document as "1589".  Next time I open up my template, is there a way to have that Quote Number cell auto-populate for "1590" using a formula?

Thanks!

5 Replies

# Re: Formula Help

This needs a bit of VBA.

Step 1: in your default file location for Excel, create a text file named Quote.txt

Open Quote.txt in Notepad and enter the last used quote number, then save and close it.

Step 2: open the template (don't create a new workbook from it).

Press Alt+F11 to activate the Visual Basic Editor.

Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left.

Copy the following code into the module:

``````Private Sub Workbook_Open()
Dim TextFile As String
Dim Quote As String
Dim f As Integer
f = FreeFile
TextFile = Application.DefaultFilePath & "Quote.txt"
Open TextFile For Input As #f
Line Input #f, Quote
Close #1
Quote = Val(Quote) + 1
Open TextFile For Output As #f
Print #f, Quote
Close #f
' Change the name of the worksheet and the address of the cell
' to the ones where you store the quote number
Worksheets("Sheet1").Range("A1").Value = Val(Quote)
End Sub``````

Step 3:

Switch back to Excel.

Save the template as a macro-enabled template (*.xltm)

Use the new xltm template.

# Re: Formula Help

Hans, thanks for your reply.  I followed your steps but came up with an error when I open the file:

When I choose to debug, this is what happens next:

Do you have any more insight for me?  I have never even attempted to use VBA before.

Thanks!
Kate

# Re: Formula Help

Did you follow Step 1 and create a text file named Quote.txt in the default file location for Excel (the folder that comes up by default in the Open and Save As dialogs)?

# Re: Formula Help

Yes, I think.  I checked my default save location:

# Re: Formula Help

That looks correct indeed! I don't understand why the code can't find the text file...

It worked when I tested the code.