Drop Down Lists
Posted by Jessica Bertine on November 15, 2001 12:18 PM
How do you construct drop down lists on excel spreadsheets and is there anyway you can link them to another spreadsheet, for example, if I had a drop down list that listed fence supplies in Spreadsheet 1. and I selected a 8" gate, which is listed on Spreadsheet 2. I would want a cost figure which is also listed on Spreadsheet2. to automatically appear on Spreadsheet 1. when choosen. Help. Note: I have never attempted drop down lists and would like additional information if I occur common problems.
Posted by Richard S on November 15, 2001 1:33 PM
Use data validation. I don't think you can reference a list on another spreadsheet, but you can do it within spreadsheet 1 on another sheet. If you create a list with all your fence supplies on sheet 2 in columnA, annd their prices in columnB, highlight the stock items in column A and click on the name box and call the range "Stock". On sheet 1, highlight the range of cells you want the drop down list, click on Data Validation, select list and in the source box type =Stock. This should provide the list of stock items.
On sheet2, highlight all the stock items AND their prices, and name the range Prices. On sheet 1, if your drop downs are in column A and you want the price to appear in column B, in cell B1 enter the formula =VLOOKUP(A1,Price,2,FALSE) and copy down as far as needed.
By naming the ranges, you can extend your list of items without having to change references by inserting rows on Sheet2 and adding items. It's a good idea to sort the price list to keep them in order and ensure VLOOKUP retunrs the correct value.
Is this what you want?
Richard
