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'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


    thanks for your help


    voki

    Are you offering a % discount according to quabtity purchased e.g >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


    Your help is greatly appreciated.


    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"
    add
    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"
    add
    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!

Register Yourself Login