DH Templates

Drop Down List in Excel 2007?

I want to create a drop down list in excel 2007. I am creating a template invoice and i wanted to have a drop down list for the product code. I also wanted it to be when I select a product code from the drop down list, the product name and price for that code will automatically appear in the field beside the product code. So basically all i needed to do was to select product code and everything else appears automatically. I also wanted to gernerate an invoice number that automatically updates itself. For example: invoice number 1000, 1001, 1002 etc.. Help

Public Comments

1. Try this for drop down example you can use altenative LOOKUP/INDEX if you wish Select cell A1 Select menu DATA Select DATA VALADATION Allow LIST Source (area of your choice anywhere on sheet (outside area used for Invoice template) Typically $B$4:$B$9 - enter Values B4=1,B5=2,B3=3 etc (Codes) - enter C4=PDA, C5=PD2 C9=PD6 (Descriptions) - enter D4= 100.25 D5=200.50 etc (Prices Use $/£ as req) Ensure you have enough space (ROWS) for all Codes and (COLUMNS) for Description and Price etc Can be different sheet if you wish DEFINE NAME for first 2 COLUMNS of this area eg DESCR (B4:C9) then DEFINE NAME for first 3 COLUMNS eg PRICE (B4:D9) Cell A2 enter =LOOKUP(A1,DESCR) Cell A3 enter =LOOKUP(A1,PRICE) As you select drop down from value from A1 Cells A2 and A3 will fill with description and price
2. I would suggest set up a table in some unused columns far to the right like AA:AC. Set your product codes in column AA beginning in row 1. Set your product names in column AB beginning in row 1. Set your prices in column AC beginning in row 1. Hide these columns. Select all cells in the Product Code column of your invoice and click on Data from the Excel 2007 menu bar. From the Data menu, locate the Data Tools panel. On the Data Tools panel, click on the Data Validation item. Select Data Validation from the menu: The Data Validation window will open. In the 'Allow' dropdown, select 'List'. In the 'Source' textbox enter a formula that contains the range of entries in column AA. For example, if you have 30 items, from AA1:AA30, then enter this formula: =$AA$1:$AA$30 Click 'OK'. Now, when you click on any cell in the Product Code column the list entries will appear. When you select one, the cell value assumes that value. Next, assuming the Product Names will be in column B in your template, beginning in row 5, enter this formula in B5: =VLOOKUP(A5,$AA$1:$AC$30,2,FALSE) Autofill this formula down through all rows containing a Product Code in the first column. In cell C5, enter this formula: =VLOOKUP(A5,$AA$1:$AC$30,3,FALSE) Autofill this formula down through the last row containing a Product Code. At this point, when you select a Product Code in the Product Code column, the Product Name and Price will autofill in the columns in the associated row. Now, as far as the auto invoice number routine, bear in mind that if you are truly creating a 'template', you will not be able to do that as a true template always opens 'clean'. To update an invoice number in the template would require the user to re-save the open workbook as a template, which opens the door for a whole host of problems. I would suggest that you just use a standard workbook and use a WorkBook_BeforeClose event handler to automatically perform any/all the following actions: Save the Invoice as a completed invoice, using an automatic naming convention. Print the invoice; number of copies as required. Clear all userinput data fields in the invoice. Increment the invoice number in the invoice number cell. Save the invoice 'template'