MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by DAVID W on January 09, 2001 10:42 AM

Is it possible to have a drop down list pop up just
by landing on a cell? And can you have the list to
pick from kept in another workbook? And when you pick
an item from the list can it be entered into the
current cell?

Posted by Tim Francis-Wright on January 09, 2001 1:18 PM

You can do this, in Excel 97 (or higher).
First, define the list as a name in the worksheet
with the cell that should have the list.
(Insert/Name/Define; type in a name for the
list under "names in workbook" and type or
select a reference under "refers to"--it's
easiest to do this with the 2 workbooks in
question both open).

Let's assume that you name the reference
as ValidStuff

Second, use Data/Validation on the cell
in question. Select List from the set of
choices, then put =ValidStuff under "Source"

This will automatically create a drop-down style
box when the user clicks on the cell, and it will
prevent typing in the cell anything not in the
list. (To prevent PASTING of anything else
requires more work--see some recent questions
if you need to do this, too.)

Good luck!