Create a Drop Down List to Populate a Cell

[jwasten]

I have a worksheet containing 2 sheets. Sheet 2 pulls data from an ODBC database and is refreshed when the user desires. Many cells in Sheet 1 are populated based on the data in Sheet 2 using, for example, =Sheet2!$A$2.

A specific cell (D5) in Sheet 1 should be populated but the result could be from any one cell between Cells L3 through L12 on Sheet 2. Is there a way to create a drop down list in Cell D5 in Sheet 1 which will display the data in Cells L3 through L12 on Sheet 2 so a selection can be made?

Thanks!

Janet

 

[Juan Pablo González]

Sure, use Data Validation on D5 in Sheet1. Select "List" from the dropdown, and in the box below, put (Or select) the Sheet2!L3:L12 reference. That should allow you to choose from any of those values.

 

[jwasten]

When I tried this, I get the message "You may not use references to other worksheets or workbooks for Data Validation criteria."

What am I doing wrong?

Janet
This message was edited by jwasten on 2002-08-29 12:46

 

[Mudface]

Try naming your range L3:L12 something like myRange, then use =myRange in the Validation source box (nb use Insert-Name-Define or the Name Box at the top left to name your cells).

 

[Brian from Maui]

Janet,

You need to name your range in sheet2 and in in the source box enter =name

 

[jwasten]

THANK YOU!!! This forum and you who answer our questions are just the best.

And now one more question - by having the drop down list, does that mean I can't type something in that cell that is not in the list?

Thanks again!

Janet

 

[Juan Pablo González]

Oops, yes, forgot about that, sorry.

 

[Mudface]

On 2002-08-29 12:56, jwasten wrote:

And now one more question - by having the drop down list, does that mean I can't type something in that cell that is not in the list?

Thanks again!

Janet

By default, yes.

You can override this if you wish by clicking on the 'Error Alert' tab of the Data Validation form, changing the 'Style' from 'Stop' to 'Information' then (to suppress the warning pop-up) unchecking the 'Show error alert...' box.

 

[Brian from Maui]

On 2002-08-29 12:56, jwasten wrote:
THANK YOU!!! This forum and you who answer our questions are just the best.

And now one more question - by having the drop down list, does that mean I can't type something in that cell that is not in the list?

Thanks again!

Janet

Short answer is yes!....But....one can copy and paste in the validated cell. To keep one from typing, you'd have to use validation in a different way.