Invoicing

  • Thread starter Thread starter Ronald
  • Start date Start date
R

Ronald

Hi,
Does anyone know how I can set an invoice up (in word or
excel) so that linked or hidden are "product lines" (eg
product code, item description and cost). I can set out
the invoice (title, date etc) but I do not know how to set
up a table and start to enter a product code, it will auto
complete the item description and cost. I would then go to
the next line and start again until the invoice is
complete. Once complete the taxes etc will be totalled and
hey presto I have an automatic invoice is printed
out.........
I hope I have explained myself.
Regards
Ronald
ronald@(remove)j-jhomes.com
 
Hi Ronald,
A table can be as simple as a list anywhere in your workbook.
Go to sheet2 and in the first row enter titles for each column.
In a1 enter, product code, b1, item description, c1, cost, d1, Retail Price?

In the second row start entering the data. a2, enter the product code or Sku
for the item. b2, the description....

Now you can assign a name for your table.
In the menus do Insert>name>define. In the top box enter a name. Table1 for
example.
In the bottom box enter the range. Like =$A$1:$E$100. This means the table
has 5 columns A through E and 100 rows.
You can modify anytime if you table dimensions change. Just remember it is
on sheet2.

Now go to your invoice and in the sku column enter a product code. I'll
assume it is in cell b6.
In the next column (c6) enter "=vlookup( b6,Table1,2,False)" without the
quotes. Press enter when done and the product code for the sku should appear
in c6.
In the third column (d6) enter "=vlookup(b6,table1,3,false)" to return the
cost.
That should get you started.

Vlookup work like this.
vlookup(this value, in this table, return data from this column in the
table,"False" means the data in the first column is not sorted in ascending
order. "True" means it is. True is the default value and is optional)
 
Thanks for your help
-----Original Message-----
Hi Ronald,
A table can be as simple as a list anywhere in your workbook.
Go to sheet2 and in the first row enter titles for each column.
In a1 enter, product code, b1, item description, c1, cost, d1, Retail Price?

In the second row start entering the data. a2, enter the product code or Sku
for the item. b2, the description....

Now you can assign a name for your table.
In the menus do Insert>name>define. In the top box enter a name. Table1 for
example.
In the bottom box enter the range. Like =$A$1:$E$100. This means the table
has 5 columns A through E and 100 rows.
You can modify anytime if you table dimensions change. Just remember it is
on sheet2.

Now go to your invoice and in the sku column enter a product code. I'll
assume it is in cell b6.
In the next column (c6) enter "=vlookup(
Click to expand...
b6,Table1,2,False)" without the
quotes. Press enter when done and the product code for the sku should appear
in c6.
In the third column (d6) enter "=vlookup
Click to expand...
(b6,table1,3,false)" to return the
 
I used the template from the Excel site (the invoice one)
and modified it to import data from my Access database and
I also put in some data Validation menus. To auto0fill teh
info in I used VLOOKUP function, since I have more than 7
fields, otherwise Id use the "If" function.
sam
 
Back
Top