Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

excel

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?

like image 727
MyNameIsKhan Avatar asked Oct 16 '13 19:10

MyNameIsKhan


3 Answers

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 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:

enter image description here

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

enter image description here

like image 84
Automate This Avatar answered Nov 08 '22 22:11

Automate This


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

like image 28
cardern Avatar answered Nov 08 '22 21:11

cardern


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.

like image 4
max Avatar answered Nov 08 '22 20:11

max