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.
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
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
^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.
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.
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Whether the value is fixed or derived makes no differenceI can't apply a name for the helper column because it contains derived values
This works for me:
=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C),1)
Palmetto
Do you know . . . ?
You can leave feedback and add to the reputation of all who contributed a helpful response to your solution by clicking the star icon located at the left in one of their post in this thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks