Excel Invoice

[Ron Bakker]

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]

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]

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]

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]

I would use a database (Ms Access), with a query generating a report (your invoice)

[Ron Bakker]

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]

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]

Just one more Q
how could I make it generate an automatic invoice number?
Cheers

[Mike]

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]

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.