PDA

View Full Version : Excel Invoice



Ron Bakker
25-11-2004, 10:03 PM
I have been looking at Microsofts invoice Templates, but none of th ones I looked at quite fit the bill.
I want it set up so I have all my inventory listed & when I want to invoice some one, I can just click through the items that I sold to that particular Person & they pop up on the invoice giving an automatic running total including Gst. Instad of manually typing each item into the invoice.
Am I making any sense?
Cheers Ron

parry
25-11-2004, 10:31 PM
Hi Ron I would say most templates rely on you choosing items from the list in the invoice itself rather than choosing the items directly from your list of products but theres no reason why you couldnt do it that way.

You could enter Y into a column against the row that contains the particular products your selling and then have a button you click that copies the ones with a Y across to the invoice (& clears the Y's ready for next time). I would say you would also need a column to indicate the number sold unless you do this yourself in the invoice.

Alternatively you can have the inventory items transferred across to the invoice one at time as you double click in the row and then you enter the number sold in the invoice.

You could probably do it all with formulas if you used an array with the destination cells selected as the target. I would need to think about a formula that could do it. Its really up to your imagination. Post again with what suits and I'll help where I can.

Ron Bakker
26-11-2004, 07:08 PM
I Kinda follow what you mean, I wish I was a bit better with my formulas.
following the help from Excel is not so intuitive when it comes to writing formulas.
I wanted it to be quite fuss free, that way it would be quick when it comes round to actually writing an invoice.
Thanks parry.

parry
27-11-2004, 08:19 AM
Hi Ron, probably the simplist method is to use Data Validation. With this you wont need to write any macros as it enables you to have a drop down list in a cell. The major bummer with it though is that the source list for the dropdown has to be in the same sheet but you could have your list out of sight in some columns way to the right such as AA etc.

Say you had your sheet set up like this:-

In cells F1:G5 you have your product list.
F1:G1 has the headings {Product, Amount}
F2:F5 has your list of products {Apple, Pear, Banana, Orange}
G2:G5 has the price for 1 item for each of those products {$5,$10,$15,$20}

In cells A1:C10 you have your invoice.
A1:C1 has the headings {Product,# Items, Amount}
A2:A8 is where you will be entering the name of the product sold
B2:B8 is where you enter a number indicating how many you have sold of that item
C2:C8 is the total amount for that product being the number sold multiplied by the price per item. Enter the following formula in C2 and drag down to C8... =IF(ISNA(VLOOKUP(A2,$F$2:$G$5,2)),"",VLOOKUP(A2,$F$2:$G$5,2)*B2)

A9 has "GST"
C9 has the formula =SUM(C2:C8)*0.125
A10 has "Total"
C10 has the formula =SUM(C2:C9)


Now to get the list of products showing in cells A2:A8 when you click on any of those cells then do the following...
1. Select cells A2:A8
2. Select Data|Validation|Settings Tab
3. In the Allow box choose List
4. Enter =$F$2:$F$5 in the source box. You could click inside this box then select cells F2:F5 with your mouse.
Thats it and the next 2 steps are optional...
5. Click the Input Message tab. This is where comment text comes up when you click on the cell. You could have a title "Enter Product" and Message "Please select a product from the list."
6. Click the Error Alert tab. This is where you would enter what message text you would like if the User broke your validation rules (ie entered something that was not in your list). Title could be "Invalid Product" with message "You have entered an invalid product. Please select a valid product from the list."

Presuming you followed the instructions above when you click on cell A2 you will get a list. Choose Apple. In cell B2 enter 4 and with any luck you should get the following results...
C2 = $20, C9 = $2.50 and C10 = $22.50

hth

beama
27-11-2004, 09:43 AM
I would use a database (Ms Access), with a query generating a report (your invoice)

Ron Bakker
27-11-2004, 01:52 PM
Thanks for that Parry, it works.
all I need to do is refine it a bit.
What does ISNA part of the code mean?
Cheers Ron

Ron Bakker
27-11-2004, 01:55 PM
Re: Beama
Would I be able to export a Access invoice as a web page? or at least something I could emailed?
Cheers Ron

Ron Bakker
27-11-2004, 01:56 PM
Just one more Q
how could I make it generate an automatic invoice number?
Cheers

Mike
27-11-2004, 02:17 PM
Access should allow you to generate the report/invoice in an email or web format. Also it can autonumber your records etc. however you set it up (eg invoice numbers).

Mike.

parry
27-11-2004, 05:05 PM
Hi NA is lookign for the error value #N/A which is what gets returned by Vlookup when it cant find a result. Its just for appearances sake to show nothing in the cell if it cant find it rather than the #N/A error.

If your wanting to keep a running track of invoices, stock control etc then yes Access may be more suited to your requirements as it can handle a lot more data and is designed to have relationships between data. In Access you would still need a form as you need somewhere to enter the sales details.

Excel works best when the data is arranged in a similar way to Access where each column is a field and each row is the data. Access allows you to have built in controls to validate each field where Excel requires a little more work if you want to nail down that side although Data Validation may be enough for you. How do you handle your stock levels?

If you wanted to do this with Excel I would have code that copied the invoice details to another sheet (with each product within the invoice representing a row but the same invoice number so they could be queried) then increment the invoice number in the code.

If you want to go down that route I would need to understand how you have laid out your Invoice and what cells hold what info. If you post that info I can write the code 4 u.

regards
Parry.

Ron Bakker
01-12-2004, 08:06 PM
Sorry about the delayed response.
Really what I want to do( if you want the big picture)
A: have a database of all the customers & what they bought.
B:notes about the customer to give a profile.
C: invoice customers online,but also have my own records on paper & PC
D: purchase history of customers.
Guess I should use Access 2000
Maybe Outlook 2000 would be good for the customer history thing as I am an online store.
Thanks for your help guys.

rafiaabdu
28-02-2005, 12:01 AM
hi parry or any one else

i have a problem while making an invoice in Microsoft excel

i have a product sheet and i want to be able to click on the product row and it appears on the invoice, i was thinking of macros (copy from the products sheet and to the invoice sheet) but what if the user wants to have more products in the future

can you please help >? With the formulas

and I want to also know how to generate automatic invoice number (I mean it generates a new number every time you open the invoice template?)

thank you, waiting for a reply

tedheath
28-02-2005, 10:30 AM
Easy peezy use cashbook form Acclaim software its bases on Access. I have just under 2000 items on inventory and it runs sweet.
site www.acclaim.co.nz or something like it.

tedheath

Parry
28-02-2005, 01:44 PM
hi parry or any one else

i have a problem while making an invoice in Microsoft excel

i have a product sheet and i want to be able to click on the product row and it appears on the invoice, i was thinking of macros (copy from the products sheet and to the invoice sheet) but what if the user wants to have more products in the future

can you please help >? With the formulas

and I want to also know how to generate automatic invoice number (I mean it generates a new number every time you open the invoice template?)

thank you, waiting for a reply

Welcome to the board. Yes those sort of things can be easily done but do you have your list of products in a different sheet than your invoice? If so that would mean selecting the product sheet then clicking on the various products then returning to the invoice. It may be better to have a drop down box on the invoice for each row where you enter a product. This could be done with Data Validation and requires no code. You may need a button on the invoice sheet (which does require code) so you can click it and store the sales details in your sales sheet. Let me know if thats what your after.

regards
Graham