Microsoft Excel spreadsheets are a useful way of keeping tabs on finances, lists and recording information. As well as listing individual items in fields, you can also create a drop-down list of items to choose from. Sometimes, you might want the drop-down list to contain items from another Excel spreadsheet or workbook. In these instances, it is possible to link to data in another spreadsheet within the box.
Open both workbooks or spreadsheets in Microsoft Excel -- the one in which you want to place the drop-down box (sheet one), and the sheet with the data you want to place in the box (sheet two). The data you want to insert should be in a list format, within a single column or row with no blank cells.
Highlight the data on sheet two that you want to place into the drop-down box on sheet one. Click the "Name" box at the end of the formula bar, and type in the name you want to use for the highlighted information. When you have finished, press "Enter."
Click on sheet one to bring it to the forefront of the screen. Select the cell in which you want to create the drop-down list.
Select the "Data" tab and choose "Data Validation" under "Data Tools." Click on "Settings." Within the "Allow" box, select "List" and press "Enter."
Find the "Source" box within the dialogue window. Type "=" and then enter the name of the list you specified in Step 2. Check the box next to "In-Cell Dropdown." Press "Enter," and the cell you chose in sheet one should now contain the specified data from sheet two.
Lists can contain a maximum of 32,767 items.
Tips and warnings
- Lists can contain a maximum of 32,767 items.