Excel Invoice Help

[FarmBoy]

Hi all. This is my first post and I must say this site has helped me more than once out of a jam.
I'm looking for help on my Excel spreadsheet / Invoice.
I sell seed corn and soybeans to other farmers and have created a spreadsheet to keep track of sales and inventory data as well as numerous other data. Each line represents 1 item sold to a customer. That same customer may possibly appear on several different lines if he purchases additional products or the same product at a later date, and not neccessarily all at 1 time so he may appear on lines 45 + 46 and also later on lines 125 and 156 and so on.
I also have a Blank Invoice on a separate sheet from the sales sheet, but still in the same workbook. My goal is to type in the Customers Name in a cell and have the rest of the blank invoice filled out from data on the sales page. I would probably want each transacaction kept seperate so if he purchased the ssame product on 2 different days I would waant it listed on 2 separate lines, (if neccessary it could be a total of all that particular variety)
If the customer only purchased 1 item 1 time I could do it, but he will sometimes purchase as much as 10 items and not all at the same time, i.e. he may purchase products at 3 different times of the year. Should I sort the data by name? The reason I dont is so that all sales activity is in order by date.
When I type in his name into the Blank Invoice I want the Invoice to show all purchase for the year.
This is where my problem lies. I'm not smart enough at this excel programming to know this trick.
Is this possible? Hope you can help.
Thanks in advance.

 

[RichardS]

Can I suggest the following;

Create a new sheet with all customers on it with the details to go in the invoice header, ie farm name, address etc. In the orders sheet, use data validation in the customer name to ensure only valid names are used from the customer sheet. On the invoice, use advanced filter to extract the relevant data. When Farmboy enters the customer name (data validation again?) the criteria range on the order sheet (could be hidden) has a formula that returns that value. Then use data validation to extract all items on the sales sheet that relate to that customer. Further, you could have a date from field so that only sales post a given date are extracted, so you don't duplicate invoices.

A recent post on the board insisted (Dave Hawley, I think) that you can extract data to a different sheet than the original data sheet.

Just a thought of the top of my head, and haven't delved too deeply into it yet.

Richard

_________________
EDIT:

Sorry, it was Aladin. Not used to him being quite so adamant Here's the thread.

http://www.mrexcel.com/board/viewtopic.php?topic=19649&forum=2
This message was edited by RichardS on 2002-08-28 21:23

 

[FarmBoy]

Ok guys Im getting the drift that this is not an easy task. Paddy suggested that each order needs to be identified with a unique #.
Each Customer does have a Customer number unique to him but I thought it would be easier to use the name only because I know the names and would have to look for the Customer# each time.
Is it possible to have a column set up beside the name or Cust# that if a customers name or Cust# appears the first time in the column the row is given the number 1 and if that same customer number appears farther down the sheet it is given the number 2 and so on. If so that would work to identify how many times a customer has ordered and give me a unique number to use.
Then I could use a MATCH formula to look for the information I want and if found then transfer that info into the Invoice sheet.
Is this idea off the mark ?

 

[RichardS]

Farmboy,

I have knocked up a quick spreadsheet example using the advanced filter I mentioned. If you would like to have a look, it may give you some ideas. Send me an email.

Richard

 

[royUK]

Hi
Can you not use the autofilter to extract the customer details.
If you have a column containing customer namess with the adjacent columns holding sales data, you can use autofilter from the Data menu to extract all rows relating to a particular customer. Then copy the info and paste into your invoice template.
Alternatively,I use a Pivot table in similar situations. I set it up to pull in the information from the data sheet and pretty it up by putting my address details and logo in Text Boxes.
This message was edited by royUK on 2002-08-29 00:16