Formula Help

%3CLINGO-SUB%20id%3D%22lingo-sub-2503594%22%20slang%3D%22en-US%22%3EFormula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2503594%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20sure%20if%20this%20formula%20even%20exists%20and%20when%20I%20try%20and%20type%20something%20into%20Google%20to%20help%20me%20find%20out%2C%20I'm%20not%20coming%20up%20with%20much.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20use%20an%20Excel%20template%20to%20create%20quotes%20for%20my%20customers%20and%20one%20of%20the%20cells%20has%20that%20customer's%20unique%20quote%20number%20in%20it.%26nbsp%3B%20Each%20completed%20quote%20is%20then%20saved%20with%20that%20unique%20quote%20number%20as%20an%20Excel%20spreadsheet%20so%20that%20I%20can%20easily%20find%20the%20saved%20documents%20by%20quote%20number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20create%20a%20formula%20that%20essentially%20counts%20up%20that%20quote%20number%20each%20time%20I%20open%20the%20template%3F%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20example%3A%20if%20I%20completed%20and%20saved%20a%20quote%20number%201589%20as%20an%20Excel%20Template%2C%20I%20would%20have%20typed%20%221589%22%20in%20the%20cell%20for%20%22Quote%20Number%22%20and%20saved%20my%20document%20as%20%221589%22.%26nbsp%3B%20Next%20time%20I%20open%20up%20my%20template%2C%20is%20there%20a%20way%20to%20have%20that%20Quote%20Number%20cell%20auto-populate%20for%20%221590%22%20using%20a%20formula%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2503594%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2503782%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2503782%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1092432%22%20target%3D%22_blank%22%3E%40katemelvin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20needs%20a%20bit%20of%20VBA.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%201%3A%20in%20your%20default%20file%20location%20for%20Excel%2C%20create%20a%20text%20file%20named%20Quote.txt%3C%2FP%3E%0A%3CP%3EOpen%20Quote.txt%20in%20Notepad%20and%20enter%20the%20last%20used%20quote%20number%2C%20then%20save%20and%20close%20it.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%202%3A%20open%20the%20template%20(don't%20create%20a%20new%20workbook%20from%20it).%3C%2FP%3E%0A%3CP%3EPress%20Alt%2BF11%20to%20activate%20the%20Visual%20Basic%20Editor.%3C%2FP%3E%0A%3CP%3EDouble-click%20ThisWorkbook%20under%20Microsoft%20Excel%20Objects%20in%20the%20Project%20Explorer%20pane%20on%20the%20left.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20Dim%20TextFile%20As%20String%0A%20%20%20%20Dim%20Quote%20As%20String%0A%20%20%20%20Dim%20f%20As%20Integer%0A%20%20%20%20f%20%3D%20FreeFile%0A%20%20%20%20TextFile%20%3D%20Application.DefaultFilePath%20%26amp%3B%20%22Quote.txt%22%0A%20%20%20%20Open%20TextFile%20For%20Input%20As%20%23f%0A%20%20%20%20Line%20Input%20%23f%2C%20Quote%0A%20%20%20%20Close%20%231%0A%20%20%20%20Quote%20%3D%20Val(Quote)%20%2B%201%0A%20%20%20%20Open%20TextFile%20For%20Output%20As%20%23f%0A%20%20%20%20Print%20%23f%2C%20Quote%0A%20%20%20%20Close%20%23f%0A%20%20%20%20'%20Change%20the%20name%20of%20the%20worksheet%20and%20the%20address%20of%20the%20cell%0A%20%20%20%20'%20to%20the%20ones%20where%20you%20store%20the%20quote%20number%0A%20%20%20%20Worksheets(%22Sheet1%22).Range(%22A1%22).Value%20%3D%20Val(Quote)%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStep%203%3A%3C%2FP%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20template%20as%20a%20macro-enabled%20template%20(*.xltm)%3C%2FP%3E%0A%3CP%3EUse%20the%20new%20xltm%20template.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2506784%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2506784%22%20slang%3D%22en-US%22%3E%3CP%3EHans%2C%20thanks%20for%20your%20reply.%26nbsp%3B%20I%20followed%20your%20steps%20but%20came%20up%20with%20an%20error%20when%20I%20open%20the%20file%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22katemelvin_0-1625142158442.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292864i827C6213229AB350%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22katemelvin_0-1625142158442.png%22%20alt%3D%22katemelvin_0-1625142158442.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhen%20I%20choose%20to%20debug%2C%20this%20is%20what%20happens%20next%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22katemelvin_1-1625142193982.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F292865i44B17DF7AC02F2BA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22katemelvin_1-1625142193982.png%22%20alt%3D%22katemelvin_1-1625142193982.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EDo%20you%20have%20any%20more%20insight%20for%20me%3F%26nbsp%3B%20I%20have%20never%20even%20attempted%20to%20use%20VBA%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%3CBR%20%2F%3EKate%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@katemelvin 

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.

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

katemelvin_0-1625142158442.png

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

katemelvin_1-1625142193982.png

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

 

Thanks!
Kate

 

@katemelvin 

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)?

@Hans Vogelaar 

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

katemelvin_0-1625143296106.pngkatemelvin_1-1625143479207.png

 

@katemelvin 

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

It worked when I tested the code.