# Help creating complex invoice with menus options and quantit

• Hi guys and gals,

I need help creating complex invoice that will contain menus with product names and be tied with the specific pricing for appropriate quantities.

In essence I wold like to open spreadsheet, select a product from a lulldown list, and enter quantity of the item bought and for the formula to get the price for that quantity and total it at the bottom. (there would have to be 4 different tiers of prices).

I know basic programming but am at a loss how to start on this one. Any help or tips would be gratly appreciated. If there is a specific section in the excel help files that deals with this topic please let me know which one it is so i can look it up.

Many thanks folks
voki

• hmmmmm

:o

this sounds like more of a "project" than specific problem....

However, it does sound as if you need basic help with data validations (dropdowns) and VLOOKUP (matching prices), rather than any programming per se.....

lets deal with your dropdown list first :

1) Which cell(s) contain your drop-down list ?

2) What is your list and where is it ?

• For the drop down lists you can use Validation in the Data Menu. Create a sheet that holds all your tables and use range names to refer to them.

Look at using VLOOKUP function to find prices for a selected item.

Use Protection to ensure the user only changes things he/she is allowed to.

HTH

Neale

There are three types of people in this world.
Those who can count and those who can&#039;t.

• thans for the tips guys.

here is more specifics:

cell 1 should contain full product names
product1
product2
...

cell 2 next to it should contain quantity that will determine the pricing (appropriate rebate based on quantity)
price1 if 1-100 units
price2 if 200-300 units
etc for 3 price levels

cell 3 should keep a subtotal for that line

product 1 | quantity | price1 | subtotal

as far as the listing of products goes i can easily put it on the worksheet in the same file as there are only about a dozen products. i am more puzzled how to do an IF this quantity THEN this price part of the formula (if that is the appropriate way to go about this).

the rest I can figure our by taking a sum of vertical lines

voki

• Are you offering a % discount according to quabtity purchased e.g &gt;10 less 10% or a variety of different prices.
If you post an example of your pricing I will have a look at it. I have several different invoice esamples which I should be able to adapt.

• Here is the product list that I wish to be in pulldown menu:
Valve 1 - 1/2”
Valve 1 - 3/8”
Valve 1 - M20 x 1,5
Valve 1 - M18 x 1,5
Valve 2 - 1/2”
Valve 2 - 3/8”
Valve 2 - M20 x 1,5
Valve 2 - M18 x 1,5
Valve 2 - 3/4"

Here is the pricing (same price for both product 1 and product 2):
Price 1 - €1.35 [quantity 1-999 units)
Price 2 - €1.35 - 25% [quantity 1,000-4,999 units)
Price 3 - €1.35 - 27.5% [quantity 5,000-9,999 units)
Price 4 - €1.35 - 30% [quantity 10,000+ units)

Also
this order of cells is not an imperative

product 1 | quantity | price1 | subtotal

whatever is the easiest way to accomplish this mathematically

Voki:spin:

• Attached is a quick example using Data Validation for picking from a list.

It uses a list of products and a discount list. You could make the discount automatic by using nested IF statements

• some concrete examples :

let's say your invoice looks something like this, with products, quantities and amounts

you want dropdowns for your products, the user inputs the quantities, and you want the amounts to automatically insert themselves :

<CENTER&gt;<TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%&gt;<TR&gt;<TD COLSPAN=5 BGCOLOR=#0C266B &gt;<TABLE ALIGN=CENTER BORDER=0 WIDTH=100%&gt;<TR&gt;<TD ALIGN=LEFT&gt;<FONT COLOR=WHITE&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD ALIGN=RIGHT&gt;<FONT COLOR=WHITE SIZE=2&gt;___Running: xl97 : OS = Windows (32-bit) 4.90</FONT&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=#D4D0C8 COLSPAN=5&gt;<TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD ALIGN=RIGHT VALIGN=MIDDLE&gt;<FORM NAME='formCb7552370787'&gt;<INPUT TYPE='Button' NAME='btCb5705426622' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb0487398093.sltNb8859926925.value);'&gt;</FORM&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=WHITE COLSPAN=5&gt;<TABLE BORDER=0&gt;<TR&gt;<Form name='formFb0487398093'&gt;<TD WIDTH=5% ALIGN=CENTER BGCOLOR=White&gt;<SELECT NAME='sltNb8859926925' onChange='document.formFb0487398093.txbFb1960511720.value = document.formFb0487398093.sltNb8859926925.value'&gt;<option value='=SUM(C2:C10)'&gt;C12</select&gt;</TD&gt;<TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 &gt;<B>=</B&gt;</TD&gt;<TD ALIGN=LEFT BGCOLOR=White&gt;<input type='text' name='txbFb1960511720' size='120' value='=SUM(C2:C10)'&gt;</TD&gt;</form&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;A</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;B</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;C</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;D</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;1</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Product 1</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Center VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Quantity</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Price 1</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;8</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;9</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;10</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#CCFFCC&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;11</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;12</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;total</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<A HREF=javascript:alert('=SUM(C2:C10)')&gt;<FONT FACE=Arial COLOR=#000000&gt;0</FONT&gt;</A&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;13</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD COLSPAN=5&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TABLE&gt;<BR&gt;<FONT COLOR=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT COLOR=RED SIZE=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT&gt;<BR&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;The above image was automatically generated by [HtmlMaker [email protected]]</FONT&gt;<FONT COLOR=#339966 SIZE=1&gt;If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]&gt;Ivan F Moala</A&gt; All credit to <A HREF=mailto:[email protected]&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

firstly :

<CENTER&gt;<TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%&gt;<TR&gt;<TD COLSPAN=6 BGCOLOR=#0C266B &gt;<TABLE ALIGN=CENTER BORDER=0 WIDTH=100%&gt;<TR&gt;<TD ALIGN=LEFT&gt;<FONT COLOR=WHITE&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD ALIGN=RIGHT&gt;<FONT COLOR=WHITE SIZE=2&gt;___Running: xl97 : OS = Windows (32-bit) 4.90</FONT&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=#D4D0C8 COLSPAN=6&gt;<TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD ALIGN=RIGHT VALIGN=MIDDLE&gt;<FORM NAME='formCb2339942116'&gt;<INPUT TYPE='Button' NAME='btCb9273296640' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb4473621504.sltNb5698059465.value);'&gt;</FORM&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=WHITE COLSPAN=6&gt;<TABLE BORDER=0&gt;<TR&gt;<Form name='formFb4473621504'&gt;<TD WIDTH=5% ALIGN=CENTER BGCOLOR=White&gt;<SELECT NAME='sltNb5698059465' onChange='document.formFb4473621504.txbFb5434202903.value = document.formFb4473621504.sltNb5698059465.value'&gt;<option value=(productlist)&gt;H2</select&gt;</TD&gt;<TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 &gt;<B>=</B&gt;</TD&gt;<TD ALIGN=LEFT BGCOLOR=White&gt;<input type='text' name='txbFb5434202903' size='120' value=Product&gt;</TD&gt;</form&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;H</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;I</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;J</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;K</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;L</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Product</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 1 - 1/2” </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 1 - 3/8” </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 1 - M20 x 1,5 </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 1 - M18 x 1,5 </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 2 - 1/2” </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;8</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 2 - 3/8” </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;9</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 2 - M20 x 1,5 </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;10</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 2 - M18 x 1,5 </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;11</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FF99CC ALIGN=Left VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Valve 2 - 3/4" </FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;12</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;13</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;(productlist)</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD COLSPAN=6&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TABLE&gt;<BR&gt;<FONT COLOR=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT COLOR=RED SIZE=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT&gt;<BR&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;The above image was automatically generated by [HtmlMaker [email protected]]</FONT&gt;<FONT COLOR=#339966 SIZE=1&gt;If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]&gt;Ivan F Moala</A&gt; All credit to <A HREF=mailto:[email protected]&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

put your list of products somewhere and name that list.....

highlight the list
insert
name
define
in the "name" box, call it "productlist"
hit okay

check it's set up as follows :

edit
goto
enter "productlist"
hit enter

it should highlight your range of products

so, now, you have a list of products, with a name "productlist".

Now, highlight the pink area (on my example invoice, first image) where you want your dropdowns to appear

data
validation
settings
allow
list
scource =productlist
okay

you will now have validated those pink cells to only allow entries that are in your productlist....... click on any of them and your drop down arrow should appear

when you click on it, your list should appear

okay so far ?

good

your next column is for amounts....

then your next column (in green on my invoice example) is where you want the prices to be entered based on quantities)

Again, somewhere safe, enter the following table :

<CENTER&gt;<TABLE ALIGN=CENTER BORDER=1 BORDERCOLOR=#C0C0C0 CELLSPACING=0 WIDTH=60%&gt;<TR&gt;<TD COLSPAN=6 BGCOLOR=#0C266B &gt;<TABLE ALIGN=CENTER BORDER=0 WIDTH=100%&gt;<TR&gt;<TD ALIGN=LEFT&gt;<FONT COLOR=WHITE&gt;<B&gt;Microsoft Excel - Book1</B&gt;</FONT&gt;</TD&gt;<TD ALIGN=RIGHT&gt;<FONT COLOR=WHITE SIZE=2&gt;___Running: xl97 : OS = Windows (32-bit) 4.90</FONT&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=#D4D0C8 COLSPAN=6&gt;<TABLE BORDER=0 ALIGN=CENTER VALIGN=MIDDLE HEIGHT=10 WIDTH=100%&gt;<TR&gt;<TD&gt;(<U&gt;F</U&gt;)ile (<U&gt;E</U&gt;)dit (<U&gt;V</U&gt;)iew (<U&gt;I</U&gt;)nsert (<U&gt;O</U&gt;)ptions (<U&gt;T</U&gt;)ools (<U&gt;D</U&gt;)ata (<U&gt;W</U&gt;)indow (<U&gt;H</U&gt;)elp</TD&gt;<TD ALIGN=RIGHT VALIGN=MIDDLE&gt;<FORM NAME='formCb3414265196'&gt;<INPUT TYPE='Button' NAME='btCb0131056580' value='Copy Formula' onClick='window.clipboardData.setData("Text",document.formFb5317475802.sltNb1643179507.value);'&gt;</FORM&gt;</TD&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD BGCOLOR=WHITE COLSPAN=6&gt;<TABLE BORDER=0&gt;<TR&gt;<Form name='formFb5317475802'&gt;<TD WIDTH=5% ALIGN=CENTER BGCOLOR=White&gt;<SELECT NAME='sltNb1643179507' onChange='document.formFb5317475802.txbFb4442305190.value = document.formFb5317475802.sltNb1643179507.value'&gt;<option value=(pricelist)&gt;E2</select&gt;</TD&gt;<TD WIDTH=3% ALIGN=RIGHT BGCOLOR=#D4D0C8 &gt;<B>=</B&gt;</TD&gt;<TD ALIGN=LEFT BGCOLOR=White&gt;<input type='text' name='txbFb4442305190' size='120' value=Quantity&gt;</TD&gt;</form&gt;</TR&gt;</TABLE&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;E</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;F</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;G</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;H</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;I</CENTER&gt;</B&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;2</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Quantity</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;Price</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;3</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;0</FONT&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;1.35</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;4</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;1000</FONT&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;1.01</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;5</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;5000</FONT&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;0.98</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;6</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;10000</FONT&gt;</TD&gt;<TD BGCOLOR=#CCFFCC ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;0.95</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;7</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD WIDTH=1% BGCOLOR=#D4D0C8 ALIGN=CENTER&gt;<B&gt;<CENTER&gt;8</CENTER&gt;</B&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF ALIGN=Right VALIGN=BOTTOM &gt;<FONT FACE=Arial COLOR=#000000&gt;(pricelist)</FONT&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;<TD BGCOLOR=#FFFFFF&gt;<BR&gt;</TD&gt;</TR&gt;<TR&gt;<TD COLSPAN=6&gt;<U&gt;Sheet1</U&gt;</TD&gt;</TR&gt;</TABLE&gt;<BR&gt;<FONT COLOR=#339966&gt;To see the formula in the cells just click on the cells hyperlink or click the Name box</FONT&gt;<BR&gt;<FONT COLOR=RED SIZE=2&gt;PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! Otherwise, the error of JavaScript occurs.</FONT&gt;<BR&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;The above image was automatically generated by [HtmlMaker [email protected]]</FONT&gt;<FONT COLOR=#339966 SIZE=1&gt;If you want FREE SOFT, <A HREF=http://www28.tok2.com/home/corosuke/HtmlMaker.htm&gt;click here</A&gt; to download</FONT&gt;<BR&gt;<FONT COLOR=#339966 SIZE=1&gt;This code was graciously allowed to be modified: by <A HREF=mailto:[email protected]&gt;Ivan F Moala</A&gt; All credit to <A HREF=mailto:[email protected]&gt;Colo</A&gt;</FONT&gt;<BR&gt;</CENTER&gt;

highlight the green area
insert
names
enter the name "pricelist"
okay

you will now have added a named range called "pricelist" which we can use in the price section of the invoice as follows :

in cell C2 enter the following formula:

=VLOOKUP(B2,pricelist,2)*B2

this is saying :

look at the amount in B2
look down the first column in my price list
when you find it or the next lowest entry
bring back what's in the 2nd column (the price)
multiply this value by B2 (the amount ordered)

you can copy (or drag) this formula down the whole green area in your invoice

this should all get you started with the basics of your requirements - I'd just recommend you do each step carefully and slowly, posting back if anything doesn't work

Good luck

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!