How to create drop down list in Excel where each list item is based on two columns?

[matkiros]

In Sheet1 I have the following table:

A1: Product ID B1: Description
A2: 1101 B2: Eggs
A3: 4367 B3: Oranges
A4: 5433 B4: Cereals
A5: 7789 B5: Rags

In sheet 2, cells in column A will contain a drop-down list where the user can choose a product. However, the list items should look like this:

1101 (Eggs)
4367 (Oranges)
5433 (Cereals)
7789 (Rags)

Is this possible with just an Excel formula or do I need a macro? I used to make drop-down lists by giving names to ranges but that only works when my list items need info only from a single column.

[Palmetto]

You'll need to use a helper column to join A & B values into the list item format you want.

Say we use column-C
C2: =A2& " ("&B2&")"

On Sheet2, use Data validation, list option and set the list reference to the cells in the helper column

[matkiros]

^I tried your soution and it seems sensible, but it turns out I can't apply a name for the helper column because it contains derived values. Any workaround? Here's what I'm trying to set in Insert > Name > Define > Refers to:

=OFFSET(Sheet1!$C$1,1,0,COUNTA($C:$C),1)

That works perfectly with data in columns A and B but I guess that's because they are givens, not derived values.

[Fotis1991]

Hi

If i am not missing something, then i think that you can do it with easier way.

Take a look to the example.

Hope to helps you.

[Palmetto]

I can't apply a name for the helper column because it contains derived values

Whether the value is fixed or derived makes no difference

This works for me:

=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C),1)