[excel] Excel: Creating a dropdown using a list in another sheet?

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?

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:

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

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.

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.

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.