Service Memo

[adam]

Hi Anyone

I have created a Service Memo template in a workbook made in Excel 2007; where I have three sheets.

Sheet 1 Service Memo
Here is how it looks like

SERVICE MEMO
Serial No

Customer ID Receipt No. Price Type
Name Bill No. Receipt Date
Age/Sex Bill Date User ID
Address Bill User Request No.

Item Code Category Description Price Qty Total
0003 Fruits Mango 12.00 01 12.00
0005 Vegetable Carrot 13.00 02 26.00
Total Thirty Eight Dollars & No Cent 38.00

Sheet 2 Products list

This Sheet has a table with the following columns
Code Description Price

for example
Code Description Price
Fruits Local Foreigner
001 Apple 05 10
002 Orange 10 20
003 Mango 15 30
Vegetables
004 Cabbage 05 10
005 Carrot 10 20
006 Cucumber 15 30

Sheet 3 Customer List
Customer ID Name Address D.O.B Age Sex
C1234 Smith Fifth Avenue 18/02/1965 58 Male

I have applied the Vlookup function in the Service Memo so that when I enter the Customer ID, the customer detail appears in the service memo & when I write the Item Code in the table on service memo the item name and price appears.

What I want to know is how to send the information that I put on the service memo into another sheet as a database for each service memo.

So that I could make a pivot table from the database sheet, in order to make Sales Statistics for each individual product.

I would be pleased if you could help me to get through.

I would be also pleased if you could guide me with what formula or VBA code I have to apply if i want to reprint a prevoiusly made service memo for a particular customer at a later date.

Thanks in Advance.

Best Regards
Adam Smith

[HansV]

Hi Adam,

Welcome to Eileen's Lounge!

You have attached a Word document with the text of your post. Did you intend to attach a copy of the workbook?

[adam]

Hi Hans,

Yeah I attached a Word document to show how the fields are in the memo. and to show the content of different sheets. I would be happy if you could help me with the subject,

Regards
Adam

[HansV]

It would help if you could post (a copy of) the workbook, so that Loungers can try out solutions.

[adam]

Hi Hans,
Attached, pls find the copy of the document.

regards
Adam

[HansV]

Your application would be more suitable for a database application such as Microsoft Access, where you can create a relational table structure. But if you prefer to use Excel, I would create a memos table and an order details table in separate sheets, and use that to populate the service memo, instead of the other way round.

[adam]

that would be fine with me Hans. I would be happy if you could do the needful.
Thanks in advance.
Regards
Adam

[HansV]

We can't produce a complete application for you, that's beyond the scope of this board, but the attached version should give you sufficient ideas. If you select a different serial number from the dropdown list in D3 on the Service Memo sheet, you'll see the rest being filled in automatically.

ServiceMemo.xlsm

[adam]

thanks for the help. I really appreciate it. I would let you know the progress after analyzing your modification.

Once again thanks for the help

Regards
Adam