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.

 

[FarmBoy]

Hi again sorry I forgot to mention that I only understand excel programming not VBA . I know it could have probably been done better in a database but haven't figured them out either. Thanks

 

[PaddyD]

FarmBoy,

welcome to the board!

it's not going to be easy to answer all your problems at once - the best way to proceed will be to work out how you are going to do this, then post with individual problems as they arrise.

That said, the following might help:

You've got a data range, and an invoice sheet. I assume that the invoice is set up to 'look nice' when it prints. It will be fairly easy to get the data onto the invoice sheet for a particular customer (vlookup or something similar). The problem is - how big does the invoice sheet have to be? The reason this is a eproblem is as follows:

1) we can't use VBA
2) only VBA can dynamically change the layout of a spreadsheet (e.g. add / remove rows)
3) we need to return the info to the invoice using formulas
4) we don't know how many formulas we need in advance


One easy way round this will be to make a guess at the greatest number of orders someone is likely to make in a year & make your invoice area this big + some...Then, you can delete the extra rows for each invoice as necessary depending on te nmumber of actual orders...Yoiu can add chacks to make sure you've got all the rows returned...

I could go on...how about posting back with more details of how your data is set up - how many columns of data you have, which ones you want in the invoice page etc.

Paddy

 

[Brian from Maui]

Farmboy,

Check this MS site for templates for invoices

http://search.office.microsoft.com/result.aspx?qu=excel template

Should get you started.

 

[FarmBoy]

Hi PaddyD.
Thanks for the prompt reply. My Order Data Sheet contains 38 Columns of data from which I will be pulling information from. The number of rows obviously will be determined by how successfull the sales season is.
On my Invoice which is already finished and yes it "looks pretty" :wink: I will need to bring in infromation such as Farm Name, Phone #, Address, etc. from the Order Data sheet. Htat I can do with no problem.
What I need to do is have the individual lines brought over onto the Invoice page. The maximum number I have allowed for is 10 lines at present. "If" that number ever needed to be increased I quess I would have to add a coouple of lines, but for arguments sake lets say the maximum number of purchases by 1 customer in a year will be 10 so 10 lines of data "max." will be brought into this Invoice.
I think I can do everything else except this searching and copying (if I may call it that)?
Hope this clarifies your request
Thanks FarmBoy

 

[FarmBoy]

Hello again.
Just realised that I'm still pretty vague in my posting. Hope this makes my intentions clearer.
1- I enter Customer Name in Cell "C8" on Invoice Page. If that name matches the Customer Name in Column A on the "Orders Page" (say line 50) then I want the info matching the order to be copied to line 15 on the "Invoice".
2- Therefore the Ordered Qty from line 50 in cell "S50" would be copied to cell "C15" (on the Invoice page) and the size from "T50" would be copied to "D15".
The Delivered Qty from "V50" copied to "E15" and the Delivered Size copied from "W50" to cell "F15".
The Returns Qty would be copied from "X50" to "G15".
The Variety "Name" copied from cell "E50" to cell "I15" and the Trait from cell "F50" to "H15".
The "Price per Unit" copied from cell "AD50" to "M15" and the "Total Amount" from cell "AE50" to "N15".
There are a few more in the same line to be copied but you get the idea of what I'm talking about now I hope.
3- Now if that same customer has another order on line 60 how do I pull that data out to be entered on line 16? As I mentioned earlier there could be up to 10 lines of data that I want copied or maybe only 1 - 2
The "Customer Information" would go into lines 8 - 12.
All "Data" on the Invoice would be copied to the cells in lines 15 to line 24.
Does this help ?

 

[Brian from Maui]

Something like this?

Book1
	A	B	C	D	E
1	Name	Qty	Size	Variety	Trait
2	Farmboy	200	Medium	Soybean	Soy
3
4	Primaris	100	Small	Corn	Blue
5	Farmboy	200	Medium	Soybean	Soy
6	PaddyD	300	Large	Corn	Sweet
Sheet1

 

[PaddyD]

Hi Brian,

The vlookup will work for a single record, but what about multiple records for a customer? I think the OP will need to add a coupl eof new fields here - eg customer order number - so that we've got something unique to work off in the lookups...

Paddy

 

[Brian from Maui]

Paddy,

Yep you're right! As XL Dennis said, there are no stupid questions, only stupid answers!

 

[PaddyD]

I disagree - there can be stupid questions. And all answers a correct, tey just might be in search of the right question!

Anyway, I agree with the vlookup appraoch, just the I think the OP will have to review how his data is set up...

I thought a fairly easy way would be to have a unique record ID built out of the combination of customer ID & order number, eg for customer A, ifrst order is A1, second is A2 etc. Could then use the order number both to provide a unique value to lookup, and as a way of identifying how many rows were neede in the invoice.

If there isn't a unique key on the order data, I think we'll be forced into a VBA solution!

Paddy