Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Invoice Templates in Excel
I am using an invoice template - based on excel. The date field automatically
updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#2
|
|||
|
|||
Hi Sue
did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#3
|
|||
|
|||
Hi Julie
I 'inherited' the invoice template - but I believe it was created from a standard MS office one - it's file path is office\library\invdb.xls. Does that help at all? Thanks for your help so far. Sue@Yes "JulieD" wrote: Hi Sue did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#4
|
|||
|
|||
Hi Sue
mmm, okay, what's happening is that the current date is being put in by a formula either =NOW() or =TODAY() and being a formula / function these will update when you open the workbook, as you have found. Basically, IMHO there's three approaches you can take to this situation: 1) delete the formula out of the date field and type the date when you raise an invoice - using the short cut key combination of control & ; makes this relatively fast 2) when you've created the formula, click in the cell and copy the date, then, stay in the cell and choose edit / paste special - values and this will change the formula into a value 3) write some code so that when you close the workbook it does #2 for you. if you would like a code solution, be aware you will need to change your security settings to medium (low is never recommened) and when you open the template / workbooks in future you'll be prompted to enable macros, which you'll need to do to for option 3 to work. If you want to use option 3, if you let me know the name of the worksheet and the cell reference of the date, i'll (or maybe someone else) will help out with the code you need to use. Cheers JulieD "Sue@Yes" wrote in message ... Hi Julie I 'inherited' the invoice template - but I believe it was created from a standard MS office one - it's file path is office\library\invdb.xls. Does that help at all? Thanks for your help so far. Sue@Yes "JulieD" wrote: Hi Sue did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#5
|
|||
|
|||
Julie/Sue
If it is based upon the INVOICE.XLT provided by Spreadsheet Solutions in a version older than Excel 2002, it will have a macro that places the date into the cell. Sub Specific_AutoStart() Range("data1").Value = Date End Sub "data1" is the the named cell(L12) where the date is entered. Do you get the macro warning when opening? If so, you have the older version. The VBA project is passworded with "chzdood"(no quotes). Go in and rem out the code by placing an apostrophe before each of the 3 lines. If no macro warning, it would be the newer non-macro version and Julie's formula advice would most likely be correct, although the newer version(Sales Invoice) I have with 2002 and 2003 does not have date inserted upon opening. i.e. no =NOW() or =TODAY() in that cell. Gord Dibben Excel MVP On Thu, 24 Mar 2005 00:39:11 +0800, "JulieD" wrote: Hi Sue mmm, okay, what's happening is that the current date is being put in by a formula either =NOW() or =TODAY() and being a formula / function these will update when you open the workbook, as you have found. Basically, IMHO there's three approaches you can take to this situation: 1) delete the formula out of the date field and type the date when you raise an invoice - using the short cut key combination of control & ; makes this relatively fast 2) when you've created the formula, click in the cell and copy the date, then, stay in the cell and choose edit / paste special - values and this will change the formula into a value 3) write some code so that when you close the workbook it does #2 for you. if you would like a code solution, be aware you will need to change your security settings to medium (low is never recommened) and when you open the template / workbooks in future you'll be prompted to enable macros, which you'll need to do to for option 3 to work. If you want to use option 3, if you let me know the name of the worksheet and the cell reference of the date, i'll (or maybe someone else) will help out with the code you need to use. Cheers JulieD "Sue@Yes" wrote in message ... Hi Julie I 'inherited' the invoice template - but I believe it was created from a standard MS office one - it's file path is office\library\invdb.xls. Does that help at all? Thanks for your help so far. Sue@Yes "JulieD" wrote: Hi Sue did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#6
|
|||
|
|||
thanks Gord
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Julie/Sue If it is based upon the INVOICE.XLT provided by Spreadsheet Solutions in a version older than Excel 2002, it will have a macro that places the date into the cell. Sub Specific_AutoStart() Range("data1").Value = Date End Sub "data1" is the the named cell(L12) where the date is entered. Do you get the macro warning when opening? If so, you have the older version. The VBA project is passworded with "chzdood"(no quotes). Go in and rem out the code by placing an apostrophe before each of the 3 lines. If no macro warning, it would be the newer non-macro version and Julie's formula advice would most likely be correct, although the newer version(Sales Invoice) I have with 2002 and 2003 does not have date inserted upon opening. i.e. no =NOW() or =TODAY() in that cell. Gord Dibben Excel MVP On Thu, 24 Mar 2005 00:39:11 +0800, "JulieD" wrote: Hi Sue mmm, okay, what's happening is that the current date is being put in by a formula either =NOW() or =TODAY() and being a formula / function these will update when you open the workbook, as you have found. Basically, IMHO there's three approaches you can take to this situation: 1) delete the formula out of the date field and type the date when you raise an invoice - using the short cut key combination of control & ; makes this relatively fast 2) when you've created the formula, click in the cell and copy the date, then, stay in the cell and choose edit / paste special - values and this will change the formula into a value 3) write some code so that when you close the workbook it does #2 for you. if you would like a code solution, be aware you will need to change your security settings to medium (low is never recommened) and when you open the template / workbooks in future you'll be prompted to enable macros, which you'll need to do to for option 3 to work. If you want to use option 3, if you let me know the name of the worksheet and the cell reference of the date, i'll (or maybe someone else) will help out with the code you need to use. Cheers JulieD "Sue@Yes" wrote in message ... Hi Julie I 'inherited' the invoice template - but I believe it was created from a standard MS office one - it's file path is office\library\invdb.xls. Does that help at all? Thanks for your help so far. Sue@Yes "JulieD" wrote: Hi Sue did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#7
|
|||
|
|||
Thanks both for your suggestions.
Have tried to follow your instructions but it still doesn't seem to get rid of the problem. Sue "JulieD" wrote: thanks Gord "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Julie/Sue If it is based upon the INVOICE.XLT provided by Spreadsheet Solutions in a version older than Excel 2002, it will have a macro that places the date into the cell. Sub Specific_AutoStart() Range("data1").Value = Date End Sub "data1" is the the named cell(L12) where the date is entered. Do you get the macro warning when opening? If so, you have the older version. The VBA project is passworded with "chzdood"(no quotes). Go in and rem out the code by placing an apostrophe before each of the 3 lines. If no macro warning, it would be the newer non-macro version and Julie's formula advice would most likely be correct, although the newer version(Sales Invoice) I have with 2002 and 2003 does not have date inserted upon opening. i.e. no =NOW() or =TODAY() in that cell. Gord Dibben Excel MVP On Thu, 24 Mar 2005 00:39:11 +0800, "JulieD" wrote: Hi Sue mmm, okay, what's happening is that the current date is being put in by a formula either =NOW() or =TODAY() and being a formula / function these will update when you open the workbook, as you have found. Basically, IMHO there's three approaches you can take to this situation: 1) delete the formula out of the date field and type the date when you raise an invoice - using the short cut key combination of control & ; makes this relatively fast 2) when you've created the formula, click in the cell and copy the date, then, stay in the cell and choose edit / paste special - values and this will change the formula into a value 3) write some code so that when you close the workbook it does #2 for you. if you would like a code solution, be aware you will need to change your security settings to medium (low is never recommened) and when you open the template / workbooks in future you'll be prompted to enable macros, which you'll need to do to for option 3 to work. If you want to use option 3, if you let me know the name of the worksheet and the cell reference of the date, i'll (or maybe someone else) will help out with the code you need to use. Cheers JulieD "Sue@Yes" wrote in message ... Hi Julie I 'inherited' the invoice template - but I believe it was created from a standard MS office one - it's file path is office\library\invdb.xls. Does that help at all? Thanks for your help so far. Sue@Yes "JulieD" wrote: Hi Sue did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
#8
|
|||
|
|||
Sue
If data in the invoice is not sensitive, send the workbook to me, not the news group. Replace the DOT and AT with appropriate symbols. I'll have a look at it. Gord On Tue, 5 Apr 2005 04:15:04 -0700, "Sue@Yes" wrote: Thanks both for your suggestions. Have tried to follow your instructions but it still doesn't seem to get rid of the problem. Sue "JulieD" wrote: thanks Gord "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Julie/Sue If it is based upon the INVOICE.XLT provided by Spreadsheet Solutions in a version older than Excel 2002, it will have a macro that places the date into the cell. Sub Specific_AutoStart() Range("data1").Value = Date End Sub "data1" is the the named cell(L12) where the date is entered. Do you get the macro warning when opening? If so, you have the older version. The VBA project is passworded with "chzdood"(no quotes). Go in and rem out the code by placing an apostrophe before each of the 3 lines. If no macro warning, it would be the newer non-macro version and Julie's formula advice would most likely be correct, although the newer version(Sales Invoice) I have with 2002 and 2003 does not have date inserted upon opening. i.e. no =NOW() or =TODAY() in that cell. Gord Dibben Excel MVP On Thu, 24 Mar 2005 00:39:11 +0800, "JulieD" wrote: Hi Sue mmm, okay, what's happening is that the current date is being put in by a formula either =NOW() or =TODAY() and being a formula / function these will update when you open the workbook, as you have found. Basically, IMHO there's three approaches you can take to this situation: 1) delete the formula out of the date field and type the date when you raise an invoice - using the short cut key combination of control & ; makes this relatively fast 2) when you've created the formula, click in the cell and copy the date, then, stay in the cell and choose edit / paste special - values and this will change the formula into a value 3) write some code so that when you close the workbook it does #2 for you. if you would like a code solution, be aware you will need to change your security settings to medium (low is never recommened) and when you open the template / workbooks in future you'll be prompted to enable macros, which you'll need to do to for option 3 to work. If you want to use option 3, if you let me know the name of the worksheet and the cell reference of the date, i'll (or maybe someone else) will help out with the code you need to use. Cheers JulieD "Sue@Yes" wrote in message ... Hi Julie I 'inherited' the invoice template - but I believe it was created from a standard MS office one - it's file path is office\library\invdb.xls. Does that help at all? Thanks for your help so far. Sue@Yes "JulieD" wrote: Hi Sue did you create the template? or is it one off the MS site - if so, if you could let us know which one, we'll be able to give you specific instructions on how to stop this behaviour. If you created it, or don't know where it came from, if you could let us know the cell reference of "today" and whether a code / vba solution is acceptable to you. Cheers JulieD "Sue@Yes" wrote in message ... I am using an invoice template - based on excel. The date field automatically updates to 'today' when I open the template. When I re-open saved invoices it does the same, which means I lose the 'correct' invoice date. How can I stop the automatic updating? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Excel Templates be modified? | Excel Discussion (Misc queries) | |||
In Excel, how do I create a one page doc, like the templates? | New Users to Excel | |||
How Do I open an excel file without Excel Viewer support | Excel Discussion (Misc queries) | |||
why templates do not load into excel 2000? | Excel Discussion (Misc queries) |