Can this be done in Excel?

[Zilla]

Say I have sheet 1 that has

Rt# O D
----------------
A B C
1 1 a b
2 2 c d

Where col A represents a route#, col B represents
orig, and col C represents dest. In essence Route 1
represents a to b, Route 2 represents c to d routes
respectively.

Now I want to have drop down list on a cell, so the
user sees "a->b", or "c->d", but when he makes a
choice, the cell will contain the route#, instead of the
actual route. IOW, it'll be more user-friendly to "see"
the "from-to" representation of the routes for the user,
but I want to use the corresponding chosen route#
for a calculation later.

Make sense?

-
- Zilla
(Remove XSPAM)

 

[Vergel Adriano]

Zilla,

How about this, in column D type:

=B1 & "->" & C1

Then, turn auto filters on (Data->Filter->AutoFilter).

That way, you see the 'from-to' representation in Column D and the
autofilter gives you the drop down list.

 

[Zilla]

Thanks.

The formula worked, but I don't get the AutoFilter feature,
even after reading the help files. Can you elaborate please?
Remember I just want the a->b shown on the drop down list,
but once the user chooses the desired orig->dest, the
corresponding route# will ultimately appear on the cell.

 

[Vergel Adriano]

Well, the auto filter will allow you to pick from 'from->to' values and when
you pick a value, rows that don't match the selected value will be hidden.
The 'a->b' value will not change to the route#. But, you would still see the
route# in the column where it's at. Take a look at the auto-filter demo so
you can see if this approach would be workable for you...

http://office.microsoft.com/en-us/excel/HA100823141033.aspx?pid=CL100570551033

 

[Debra Dalgleish]

There's a sample workbook on my web site that shows a product name and
code in the Data Validation dropdown list. After an item is selected,
the cell shows only the product name. You could adapt this to your
workbook:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for DV0005 - Data Validation "Columns"

 

[Zilla]

I think I found it, is it the Dynamic list workbook?

 

[Debra Dalgleish]

I'm not sure which file you mean. The file I referred to is named
DataValNameID.zip:

DV0005 - Data Validation "Columns" -- Data Validation dropdown displays
product name and ID; an event procedure changes the selection to product
name. (XL2000 +) DataValNameID.zip 9 kb