Excel: Creating a dropdown using a list in another sheet?

Question

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

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. (so you could fill the value of the cell with AA, QF, etc).

Is this doable?

Answer 1

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 your cell that you want a drop down to be in and goto data tab -> data validation.

Select 'List' from the 'Allow' Drop down menu.

Enter your named range like this:

Now you have a drop down linked to your range. If you insert new rows in your range everything will update automatically.

Answer 2

Yes it is. Use Data Validation from the Data panel. Select Allow: List and pick those cells on the other sheet as your source.

Answer 3

That cannot be done in excel 2007. The list must be in the same sheet as your data. It might work in later versions though.