In Sheet2, I want a particular column to have cells that, when you click them, have a dropdown that consists of values from the code-list on Sheet1. Use Data Validation from the Data panel.

List and pick those cells on the other sheet as your source. Now you have a drop down linked to your range. If you insert new rows in your range everything will update automatically. That cannot be done in excel The list must be in the same sheet as your data. It might work in later versions though.

Excel has a very powerful feature providing for a dropdown select list in a cell, reflecting data from a named region. It'a a very easy configuration, once you have done it before. Two steps are to follow:.

There is a detailed explanation of the process HERE. I was able to make this work by creating a named range in the current sheet that referred to the table I wanted to reference in the other sheet. By posting your answer, you agree to the privacy policy and terms of service.

Stack Overflow Questions Developer Jobs Documentation beta Tags Users. Sign up or log in to customize your list. Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn how to put drop down list in excel from another sheet about Stack Overflow the company Business Learn more about hiring stock market courses chicago or posting ads with us.

Log In Sign Up. Join the Stack Overflow Community. Stack Overflow is a community of 7. Join them; it only takes a minute: Creating a dropdown using a list in another sheet?

Create a drop-down list - Office Support

Let's say in Sheet1 I have a list of codes "AA", "QF", etc. MyNameIsKhan 1, buying a put option investopedia 19 Is there a way to make this data validation dropdown appear "automatically" for each cell in the column except for the top header?

Yes, just copy the cell to every other cell in the column. Alternatively, select the entire column before creating the data validation.

How to create external data validation in another sheet or workbook?

See my edit - just do the entire column. You can do how to put drop down list in excel from another sheet by clicking on the column hedder The letter up on top before clicking Data Validation and specifying your list. Try saving it as a Macro-enabled workbook and let me know if this fixes the problem. As cardern has said list will do the job. Here is how you can use a named range. Select your range and enter a new name: Select 'List' from the 'Allow' Drop down menu.

Enter your named range like this: Does referencing to named range in another sheet really work with Excel too?. I am getting an error "You cannot use references to other worksheets or workbooks for Data Validation criteria.

If you prefer to keep the source cells under the dropdown for ease of update. This way it will be presentable and it's easy to update your source cells. I can confirm that this method works in without a problem. This is the correct answer.

It cannot be done as the OP asked it; the list data must be on the same sheet. I am currently using excel and am using a named range on Sheet2 to do this very thing.

How to Create a Drop-Down in Excel in 1 Minute (or less)

This answer is not correct. Oct 28 '16 at Two steps are to follow: Create a named region, Setup the dropdown in a cell. Sign up or log in StackExchange.

Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Stack Overflow works best with JavaScript enabled. MathOverflow Mathematics Cross Validated stats Theoretical Computer Science Physics Chemistry Biology Computer Science Philosophy more 3.

Meta Stack Exchange Stack Apps Area 51 Stack Overflow Talent.