Excel: creating a dropdown using a list on another sheet?

Question

Let's say in Sheet1 I have a list of codes ("AA", "QF", etc.).

In Sheet2, I want to have cells in a specific column that, when clicked on, have cells that have a drop-down list consisting of values ​​from the code list on Sheet1. (so you can fill in the cell value with AA, QF, etc.).

Is this doable?

Answer 1

Yes it is. Use data validation in the data pane. Select "Allow: List" and select these cells on another sheet as the source.

Answer 2

As cardern said, the list will do the job.

Here's how you can use a named range.

Select a range and enter a new name:

Select the cell for which you want it to be enabled, and go to the data tab → data validation.

Select List from the Allow drop-down menu.

Enter your named range as follows:

You now have a drop-down list associated with your range. If you insert new lines in your range, everything will be updated automatically.

Answer 3

This cannot be done in Excel 2007. The list should be on the same sheet as your data. However, it may work in later versions.

Answer 4

Excel has a very powerful feature that provides a drop-down list in a cell that reflects data from a named area. This is a very simple configuration as soon as you have done this before. The following two steps must be completed:

Create a named region, Setup the dropdown in a cell. 

A detailed explanation of the process is HERE .

Answer 5

I was able to do this work by creating a named range in the current sheet that referenced the table I wanted to reference in another sheet.