data stored in one cell

Microsoft Microsoft office excel 2007 ac...
April 11, 2010 at 04:36:19
Specs: Windows XP
I want to make invoice in excel for calculating product value.I have value with VAT 18%, and with 5%.
I want the to have option to choose the product form data of products stored previosly.How to stored products with their price and VAT value and to be able to choose them when making invoice.

See More: data stored in one cell

Report •

April 11, 2010 at 09:07:04

Here is one way to do this.

On your worksheet create a table containing the list of products, with price excluding VAT, VAT percentage, and price including VAT.
For this example I created this table:

	J	K	L	M	N
4	Product	Price	VAT %	VAT	Price
		ex.VAT			incl. VAT
5	Nuts	1.50	18%	0.27	1.77
6	Bolts	0.90	18%	0.16	1.06
7	Hand	2.56	5%	0.13	2.69

Note that the product name must be in the first column in this table.
You can use formulas in columns M and N to create these values from the original price ex. VAT and the VAT %.

In the part of the worksheet you are using as an Invoice, select the cell that will contain the product name.
For this example I am using cell B5.
From the Ribbon select 'Data' and in 'Data Tools' select Data Validation.
From the drop-down select 'Data Validation' and in the dialog box that opens select List from the 'Allow:' box.
In the 'Source:' box enter the range of cells containing the names for your list.
For this example select cells J5 to J7
It will show in the box as $J$5:$J$7
Click OK

When you click on cell B5 you will now get a list of available products.
Cell C5 will contain the VAT rate (%)
Cell D5 will contain the price including VAT
Cell E5 will contain the VAT amount

Enter this formula in cell C5

Note the $ signs - they are required to allow you to drag and extend this formula and keep the cell references correct.
Select cell C5 and drag/extend it to the right to column E (cell E5)
Now adjust the offset value in the formulas in cells D5 and E5
change D5 from 3 to 5
change E5 from 3 to 4

If you want more items from your list on subsequent rows on your invoice, just select cells B5 to E5 and drag/extend them down as many rows as required.

Changing the product name in cell C5 will result in the correct prices/VAT rates showing in the adjacent cells.
You can look at the Help file for VLOOKUP to find out more about how it works.


Report •
Related Solutions

Ask Question