Invoice template with dependent drop down lists in excel
Overview
This post descibes how to use a basic invoice template I created. The invoice template let´s you use dropdown lists to quickly select products on a price list. You can easily add/remove data on "price list" sheet.
Features
- Formula calculates totals
- Find products easily with dependent drop down lists
- Drop down lists contain unique categories, no duplicates
- Ranges expand when new data is added to the price list
How to use invoice template
- Specify quantity
- Select a category
- Select a product and the formula calculates the total price.
How to copy dependent drop down list
- Select a cell containing drop down list
- Copy cell
- Right click on new cell
- Click "Paste Special..."
- Click "Validation"
- Click OK!
Download excel sample file for this tutorial.
Invoice template.xls
(Excel 97-2003 Workbook *.xls)
Recommended blog articles
- Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
- Create dependent drop down lists containing unique distinct values in excel
- Apply dependent combo box selections to a filter in excel 2007
- Create dependent drop down lists containing unique distinct values in multiple rows
Related posts:
Create dependent drop down lists containing unique distinct values in multiple rows
Basic invoice template in excel
Dependent drop down lists – Enable/Disable selection filter
Create dependent drop down lists containing unique distinct values in excel
Recommended categories
HI Oscar
This template would work well for me,however I'm using Excel 2010. For the life of me I cannot get the template to calculate the amount of new items added to the data sheet. Replacing the existing items with my item names doesn't seem to work well either. i.e. I replace all the pen category and descriptions with "my" items and the template calculates the amount for 2 of 3 items replaced. None of which makes sense...I have not monkeyed with the lookup or index formulas.
thanks
dale,
For the life of me I cannot get the template to calculate the amount of new items added to the data sheet. Replacing the existing items with my item names doesn't seem to work well either. i.e. I replace all the pen category and descriptions with "my" items and the template calculates the amount for 2 of 3 items replaced.
There is only a formula in the two first cells in the Amount column.
1. Select cell H17
2. Copy (Ctrl + c)
3. Select cell range H18:H38.
3. Paste (Ctrl + v)
You have to do the same with the drop down lists in cell C16:E16. Copy/Paste drop down lists.
Remove #N/A
The formula in cell H17 becomes:
=IFERROR(VLOOKUP(E17,vlookuptable,2)*B17,"")
Thanks for your comment!
First of all, thanks for sharing this file, it saved me a lot of time.
It did take some time to get it working for me however.
I tried to enter about 50 new products, with 12 new categories, and had to do the following things to get it working:(Excel 2007)
1. On Tab Invoice, H16, I had to enable exact matching with the vlookup. Change formula FROM:
=VLOOKUP(E16;vlookuptable;2)*B16 to =VLOOKUP(E16;vlookuptable;2;FALSE)*B16
And extended the range down.
2. I had to enable extra categories by extending the range down on Tab Calculation, A3 downward.
After these adjustments, it worked great!
Michel Jilderda,
Thank you for sharing!
I love the way this is set up but I'm brain dead when it comes to Excel drop downs. Is there a way to formulate that it will list the item but then not 'show' the amount out from it. I want to make it so the 'item' multiplies based on a guest count and does a running total at the end....I don't know if that's even possible. Thanks for your help!
Kim,
Can you provide an example?