How can I make a drop down list that stores an index value in a hidden field?

Question

I have an Excel spreadsheet where on Sheet 1 the Department Id is represented as a number and where on Sheet 2 there is a list of those numbers with the Department Name next to them.

If possible I would like some help and advice as I want to allow the user to select the Department Name from a drop down list and have it populate a hidden field with the department number.

Something like in Sheet 3 with the Department Id being stored for example in the C column on that page:

Sheet 1

Sheet 2

Sheet 3

Answer 1

This could be one approach.

If I have understood you correctly, the user will select the department name in a drop-down list in Sheet 3 and and the index number associated with that department name will appear. The user will not be limited to what name (andy, bob) it is in the adjacent column...

Result will be (notice this is a Excel Table!) :

We have the 3 sheets:

Select the data areas and click on "Format as Table", Select the option "My data has headers". Do this for all the 3 sheets.

Name your tables: "Design" -> "Table Name". I will use "Table1" for "Sheet1", "Table2" for "Sheet2" etc. It will make life easier when you link and refer in formulas. Notice that for "Sheet3" I also add a dummy column C. I name it "Index Number".

Go to "Sheet2" and select the range that you want to use as your drop-down list (in my case i choose Department Names and selected "B2:B8". Click "Formulas" -> "Name Manager" -> "New..."

This window will pop-up. Name it an easy name (I named it: "Department_Names") and check that the "Refers to:" is in this format "=TableX[Column Name]". In our case =Table2[Department]. Notice that the name manager don't use "B2:B8", rather is reference to the table column area. The last step is very important!

Go to Sheet3. Click on cell where you want to apply the drop-down list (I use B2 in the example). Click "Data" -> "Data Validation".

Data Validation window will now pop-up. Go to "List" and in the field "Source:" you write your named range as =Department_Names (the one I named "Department_Names"). The drop-down list will use the values in the range that "Department_Names" are applied to. In our case it will make a drop-down list of all the department names from "Sheet2", range B2:B8.

Go to "Sheet3" and enter the formula below in the cell C2 as shown in the picture.

Notice I use " ; "as delimiter (European version of Excel, US user will probably use" , ").

=IF([@Department]="","",IFERROR(INDEX(Table2[ID],MATCH([@Department],Table2[Department],0)),))

The first part creates our Index number:

...IFERROR(INDEX(Table2[ID],MATCH([@Department],Table2[Department],0)),)

• Return the Index number from "Table2", Column ID. INDEX(Table2[ID]
• lookup value in the MATCH formula comes from "Table3", Department (drop-down list)
• The lookup value will look in "Table2", column Department.

Will take care of 0 that will be created when nothing is selected in the "Table3", Department column (see next picture for example)

=IF([@Department]="","", ...formula...)

Formula without the =IF([@Department]="","", ...formula...)

You can hide the "Index Number" column in "Sheet3" if you don't want to have it visible. Right click on the Column C header and choose "Hide".

Only for demonstration purpose here. I create a dummy column "Only for show hidden column values". So when the user selects a Department name, it will change the index number associated to that department.

So why the use of tables and named range?

Let's say you need to add a new department. We add a department in the "Table2" and we give it ID: 7 and Department Name: Department H.

What happens in your "Sheet3" is that the drop-down list will automatically catch the new row. The range named to "Department_Names" will adjust to the table rows because it's linked to "Table2[Department]". This is also true if you decrease rows for Table2. Source

I have not tested it, but I'm quite sure that you can link a table to an external source. So when you import your data to the table (given the headers is static) the table will be updated with "Refresh All" button in Excel. Something to explore and look further into :)

Answer 2

It can be done with INDEX/MATCH.

As an example:

Sheet1 contains your lookup table.
For simplicity sake Sheet1 has numbers 1 - 6 in the range A1:A6 and A - F in the range B1:B6.
1 - 6 are the primary keys and A - F are the descriptions.

Sheet2 has Data Validation in column A: Allow List using =Sheet1!$B$1:$B$6 as the Source. This will give you the drop-down in column A.
In column B, which is hidden, you have the formula =IFERROR(INDEX(Sheet1!$A:$A,MATCH($A2,Sheet1!$B:$B,0)),"")

The MATCH formula will return the row number in Sheet1 that the description appears in.
The INDEX formula will return a reference to the cell in column A on that same row - giving the Primary Key value.
If no match is found then #N/A is returned which is dealt with by the IFERROR statement.