If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Making invoices with variable amounts
Hi,
I am using Microsoft Word 2002 on Windows XP (both Japanese language versions). I need to make lots of invoices for various amounts. The invoices are all identical except for the figures. I have to make three pages, an invoice, a receipt, and an estimate, all with the same amounts on them. I would like to automate this process. I don't need to make a lot of these invoices at once. I want to have some way to make these invoices when I need them, which is every few weeks or so. I want to input two variables (unit cost and number of units) somehow, and have the invoices made for me, including calculating the sales tax, etc. How do I go about doing this? I tried reading a book about Microsoft word but I couldn't see where to start. Of course I don't expect people to solve the whole problem for me, but any hints, directions or guidance whatsoever are very welcome. If anyone has a sample file of something similar to what I want, which I could fiddle with, that would be super. Thank you! Danny. |
#2
|
|||
|
|||
A good start would be with a predesigned invoice template to capture the
variables you mention. Try here - http://office.microsoft.com/en-us/te...469781033.aspx "Danny Wilde" wrote in message ... Hi, I am using Microsoft Word 2002 on Windows XP (both Japanese language versions). I need to make lots of invoices for various amounts. The invoices are all identical except for the figures. I have to make three pages, an invoice, a receipt, and an estimate, all with the same amounts on them. I would like to automate this process. I don't need to make a lot of these invoices at once. I want to have some way to make these invoices when I need them, which is every few weeks or so. I want to input two variables (unit cost and number of units) somehow, and have the invoices made for me, including calculating the sales tax, etc. How do I go about doing this? I tried reading a book about Microsoft word but I couldn't see where to start. Of course I don't expect people to solve the whole problem for me, but any hints, directions or guidance whatsoever are very welcome. If anyone has a sample file of something similar to what I want, which I could fiddle with, that would be super. Thank you! Danny. |
#3
|
|||
|
|||
"Rick" wrote in message
... A good start would be with a predesigned invoice template to capture the variables you mention. Try here - http://office.microsoft.com/en-us/te...469781033.aspx Thanks for your advice. It seems I should use Excel instead? I've never tried it, so I'll have to learn that. |
#4
|
|||
|
|||
Well, I don't know about that. Excel is a good choice in terms of ease in
getting the math automation (the qty and unit price to automaticlly calculate the total). But it's not very intuitive in terms of page design and layout. If you're unfamiliar with Excel, then I would stick with Word. The trick to using an invoice template in Word is to understand how to create and use Field codes in order to get the calculations. For instance, the Sales Invoice template 2nd from the bottom on the first page of that link I sent will need to be modified to include the Fields. In the total column on the first row under the header (Quantity, Description, etc.), you'll need to create a Field that looks something like this: { IF { = A2*C2 } = 0 "" "{ = A2*C2 \# "#,##0.00;(#,##0.00)"}" } Then you'll repeat this in each of the other Item rows, changing the row reference (A2*C2 becomes A3*C3 in the 2nd row, etc.). In the Subtotal cell: { =SUM(ABOVE) \# "$#,##0.00;($#,##0.00)" } In Sales Tax, put a decimal figure next to the words Sales Tax such as .075 so that it reads: SALES TAX .075 Then, in the adjacent cell in the Total column, the field: { =C12*B13 \# "$#,##0.00;($#,##0.00)" } Finally, in the Total Due cell: { =SUM(C12:C14) \# "$#,##0.00;($#,##0.00)" } These calcalulations all depend on what you type in the Quantity and Unit Price cells. They also deal with negative numbers (for a return, for instance, where you would type a -1 in the Quantity field to return an item). They'll also not display at all if the result is 0. Since Word doesn't automatically update these types of fields, you'll need to do it manually. Press Ctrl-A, then F9 to see all of the fields re-calculate. "Danny Wilde" wrote in message ... "Rick" wrote in message ... A good start would be with a predesigned invoice template to capture the variables you mention. Try here - http://office.microsoft.com/en-us/te...469781033.aspx Thanks for your advice. It seems I should use Excel instead? I've never tried it, so I'll have to learn that. |
#5
|
|||
|
|||
You've provided some great info for Danny. :-)
Personally I'd use Excel to eliminate the need to determine the cell addresses, manually create every single formula, and the need to manually recalculate. Even if someone isn't familiar with Excel I think the ease in formula creation, number formatting, etc would be easier than trying to do this in Word. If I had to use Word I could create a document with form fields to make it easier to navigate and recalculate. Also since it looks like you are familiar with Word formulas the =Sum(Above) formula may not calculate correctly in the event of empty cells or negative values so it's best to use the cell range instead. And the following will eliminate the need for an IF in the event of zero values. { =A2*C2 \# "$#,##0.00;($#,##0.00);"} Note the semi-colon at the end of the picture switch. Word supports three formats: positive;negative;zero. If you accommodate the zero format position by adding a semi-colon but leave it empty/null it will suppress the display of zeros. And Danny, if you opt to use Word for this then I recommend downloading Macros8.dot from: http://word.mvps.org/Downloads/index.htm This template has a macro called TableCellHelper which will help you determine the cell references for your formulas. To use it to help you create your invoice I recommend manually loading it since you won't use it daily. After you download and extract the template, in Word go to Tools/Templates and Add-ins and click the Browse command and navigate to where you stored the template. Once it's loaded you will see a toolbar that has a command for the Table Cell Helper macro. Note the template will not be loaded the next time you start Word however if you want to use it just go to Tools/Templates and Add-ins and place a tick in the check box so it will load. -- Please post all follow-up questions to the newsgroup. Requests for assistance by email can not be acknowledged. ~~~~~~~~~~~~~~~ Beth Melton Microsoft Office MVP Word FAQ: http://mvps.org/word TechTrax eZine: http://mousetrax.com/techtrax/ MVP FAQ site: http://mvps.org/ "Rick" wrote: Well, I don't know about that. Excel is a good choice in terms of ease in getting the math automation (the qty and unit price to automaticlly calculate the total). But it's not very intuitive in terms of page design and layout. If you're unfamiliar with Excel, then I would stick with Word. The trick to using an invoice template in Word is to understand how to create and use Field codes in order to get the calculations. For instance, the Sales Invoice template 2nd from the bottom on the first page of that link I sent will need to be modified to include the Fields. In the total column on the first row under the header (Quantity, Description, etc.), you'll need to create a Field that looks something like this: { IF { = A2*C2 } = 0 "" "{ = A2*C2 \# "#,##0.00;(#,##0.00)"}" } Then you'll repeat this in each of the other Item rows, changing the row reference (A2*C2 becomes A3*C3 in the 2nd row, etc.). In the Subtotal cell: { =SUM(ABOVE) \# "$#,##0.00;($#,##0.00)" } In Sales Tax, put a decimal figure next to the words Sales Tax such as .075 so that it reads: SALES TAX .075 Then, in the adjacent cell in the Total column, the field: { =C12*B13 \# "$#,##0.00;($#,##0.00)" } Finally, in the Total Due cell: { =SUM(C12:C14) \# "$#,##0.00;($#,##0.00)" } These calcalulations all depend on what you type in the Quantity and Unit Price cells. They also deal with negative numbers (for a return, for instance, where you would type a -1 in the Quantity field to return an item). They'll also not display at all if the result is 0. Since Word doesn't automatically update these types of fields, you'll need to do it manually. Press Ctrl-A, then F9 to see all of the fields re-calculate. |
#6
|
|||
|
|||
Ah, I like your non-IF statement better!
The Sum(Above) statement works as long as you don't leave a gap between items so in that respect, you're right in that the literal range would be better. And I'd use Excel, too, except that if you're not familiar with it, page layout is not intuitive and you only have minimal control over certain design elements such as headers and footers (although they're probably not necessary anyway for this). "Beth Melton" wrote in message ... You've provided some great info for Danny. :-) Personally I'd use Excel to eliminate the need to determine the cell addresses, manually create every single formula, and the need to manually recalculate. Even if someone isn't familiar with Excel I think the ease in formula creation, number formatting, etc would be easier than trying to do this in Word. If I had to use Word I could create a document with form fields to make it easier to navigate and recalculate. Also since it looks like you are familiar with Word formulas the =Sum(Above) formula may not calculate correctly in the event of empty cells or negative values so it's best to use the cell range instead. And the following will eliminate the need for an IF in the event of zero values. { =A2*C2 \# "$#,##0.00;($#,##0.00);"} Note the semi-colon at the end of the picture switch. Word supports three formats: positive;negative;zero. If you accommodate the zero format position by adding a semi-colon but leave it empty/null it will suppress the display of zeros. And Danny, if you opt to use Word for this then I recommend downloading Macros8.dot from: http://word.mvps.org/Downloads/index.htm This template has a macro called TableCellHelper which will help you determine the cell references for your formulas. To use it to help you create your invoice I recommend manually loading it since you won't use it daily. After you download and extract the template, in Word go to Tools/Templates and Add-ins and click the Browse command and navigate to where you stored the template. Once it's loaded you will see a toolbar that has a command for the Table Cell Helper macro. Note the template will not be loaded the next time you start Word however if you want to use it just go to Tools/Templates and Add-ins and place a tick in the check box so it will load. -- Please post all follow-up questions to the newsgroup. Requests for assistance by email can not be acknowledged. ~~~~~~~~~~~~~~~ Beth Melton Microsoft Office MVP Word FAQ: http://mvps.org/word TechTrax eZine: http://mousetrax.com/techtrax/ MVP FAQ site: http://mvps.org/ |
#7
|
|||
|
|||
I thought you might like the zero format tip. I think it's documented
but it's one I carried over from Excel. I figured if works in Excel then why not try it in Word? I agree, if you haven't used Excel then it may be more difficult but one should never miss out on a learning opportunity. :-) -- Please post all follow-up questions to the newsgroup. Requests for assistance by email can not be acknowledged. ~~~~~~~~~~~~~~~ Beth Melton Microsoft Office MVP Word FAQ: http://mvps.org/word TechTrax eZine: http://mousetrax.com/techtrax/ MVP FAQ site: http://mvps.org/ "Rick" wrote in message ... Ah, I like your non-IF statement better! The Sum(Above) statement works as long as you don't leave a gap between items so in that respect, you're right in that the literal range would be better. And I'd use Excel, too, except that if you're not familiar with it, page layout is not intuitive and you only have minimal control over certain design elements such as headers and footers (although they're probably not necessary anyway for this). |
#8
|
|||
|
|||
Thanks very much to Rick and Beth for their advice.
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
about making invoices | [email protected] | General Discussion | 1 | November 15th, 2004 11:02 PM |
Making form/report with a fixed no. of lines and variable no. or records | Chris | Setting Up & Running Reports | 8 | June 1st, 2004 08:42 AM |
NEW PROBLEM at making form/report with a fixed no. of lines and variable no. or records | Chris | Using Forms | 0 | June 1st, 2004 08:42 AM |
PROBLEM: Making form/report with a fixed no. of lines and variable no. or records | Chris | Using Forms | 0 | May 31st, 2004 11:46 AM |