Inventory and invoicing

**PMA** · 03-02-2010, 01:09 AM

G'day, new here so hopefully somebody will be able to help me. My knowledge of Excel is ok, but don't know too much about the backend stuff.

What i'm looking for or wanting if its at all possible is something for my business i've just started up (and is going quite slow, so can't afford high end pos programs at this stage) is an excel file (as I don't have access) that has an inventory list and a tax receipt section possibly using a form or something that when I do up an invoice for a customer, that it records the sale somewhere on another sheet. I'm not sure if this is possible with excel, but i'm sure somebody will tell me.

I can do up the basic stuff, like the inventory sheet, a sheet for the invoice (that when the item is entered, the price & details display), and an overview of sales, expenses etc etc.
Also I should stress that the more items are purchased, the cheaper the item becomes, so there are pricebreaks (ie: 1, 10, 25 etc but not everything has the same price break)

If anybody is able to help me or knows of something that already exists, it will be greatly appreciated.

If this isn't possible with excel, can anybody let me know what will do the job?
Cheers

**teylyn** · 03-02-2010, 01:34 AM

Hi PMA,

welcome to the forum.

It might be easier to tackle this the other way round, i.e. record your sales information in a table, and then on another sheet, create an invoice from the data stored in that table.

A table could look like this:

order ID .....Date ..... customer ID ..... item ID .... item description .... units .....unit price ..... discounts... line total

In another table (on another sheet) you'd have your customer information

customer ID .... Name .... address line1 ..... address line2 ..... etc.

Then you can have an Invoice sheet, where you select Order ID and the rest of the invoice will be populated with the information from the Sales and the Customer sheet.

Maybe start by creating the Sales and Customer tables on two separate sheets, design your invoice layout and post that file here, then we can help you populate your invoice details.

hope that helps.

**royUK** · 03-02-2010, 02:29 AM

It's all doable in Excel,using VBA,but it's a big project to docorrectly

**teylyn** · 03-02-2010, 05:13 AM

I disagree, Roy. You can build a database with Sales, Customer info, Product info on different sheets, have another sheet to display invoice info based on Order ID and fill in the invoice information. All without a single line of VBA code.

It's the direction of the data flow, that matters. If you want to start by entering the data on the invoice sheet and want it to propagate into the Sales sheet, then, yes, you'd need VBA. But if you do your data entry on the separate sheets and use the Invoice like a report that pulls data from the other sheets, then it's a piece of cake with a few Vlookups.