Making invoices with variable amounts

D

Danny Wilde

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.
 
R

Rick

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.
 
G

Guest

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/
 
R

Rick

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



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/
 
B

Beth Melton

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/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Apply Pament to invoices in form 0
Dynamic range of report 6
Printing invoices 3
Developing invoices with transaction breakdown 1
Need help in making my invoices auto count 1
attach row number to match fields 1
Expression Builder query with zero amounts 5
pivot tables 1

Top