Create a Drop Down List to Populate a Cell

jwasten

Board Regular
Joined
May 29, 2002
Messages
90
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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

Board Regular
Joined
May 29, 2002
Messages
90
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459

ADVERTISEMENT

Janet,

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

jwasten

Board Regular
Joined
May 29, 2002
Messages
90
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
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,459
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.
 

Forum statistics

Threads
1,147,679
Messages
5,742,575
Members
423,738
Latest member
AshleyKitsune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top