Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...
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" wrote in
message
...
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" wrote:

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)

Hi Zilla,

Maybe you should start over. What are you asking?
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...
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" wrote in
message
...
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" wrote:

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)

Ok Shane, here goes

Say I have sheet 1 that has route numbers (Rt#) tabulated
against the actual orig (O) and dest (D) route, like this

Rt# O D O-D
A B C D
1 1 a b a-b
2 2 c d c-d
..................
24 24 x y x-y

So Rt# 1 represents route a-b, Rt# 2 represents route c-d,
etc, or in general, Rt# n represents O-D route.

Someone suggested creating the D column that has
the formulae...
D1=B1&"-"&C1
D2=B2&"-"&C2
....for each D cell to show the, for example, a-b. So I
created a list with the D col. and called it "FromTo"

In sheet 2, when a user clicks on a cell, I want to present
the user with the "FromTo" drop down list that looks like

a-b
c-d
......
x-y

If he chooses the "c-d" route, the cell will ultimately contain the
corresponding Rt#, in this case "2".

So, in pseudo-code, Sheet2!A cells may have this formula

Ax=if(Match(FromTo choice, Sheet1!Rt#), Sheet1!Rt#, Error)

Makes a little more sense?

I'm studying Debra D's example now, but it involves VB, which,
like Excel, I'm illiterate at.

-Zilla

In sheet 1 I have a matrix of
"ShaneDevenshire" wrote in
message ...
Hi Zilla,

Maybe you should start over. What are you asking?
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...
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" wrote in
message
...
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" wrote:

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)

Like I said, Debra D's worksheets presented a VBA
solution and I'm studying that too. How do the VBA
macros run for certain cells only?

"Zilla" wrote in message
...
Ok Shane, here goes

Say I have sheet 1 that has route numbers (Rt#) tabulated
against the actual orig (O) and dest (D) route, like this

Rt# O D O-D
A B C D
1 1 a b a-b
2 2 c d c-d
.................
24 24 x y x-y

So Rt# 1 represents route a-b, Rt# 2 represents route c-d,
etc, or in general, Rt# n represents O-D route.

Someone suggested creating the D column that has
the formulae...
D1=B1&"-"&C1
D2=B2&"-"&C2
...for each D cell to show the, for example, a-b. So I
created a list with the D col. and called it "FromTo"

In sheet 2, when a user clicks on a cell, I want to present
the user with the "FromTo" drop down list that looks like

a-b
c-d
.....
x-y

If he chooses the "c-d" route, the cell will ultimately contain the
corresponding Rt#, in this case "2".

So, in pseudo-code, Sheet2!A cells may have this formula

Ax=if(Match(FromTo choice, Sheet1!Rt#), Sheet1!Rt#, Error)

Makes a little more sense?

I'm studying Debra D's example now, but it involves VB, which,
like Excel, I'm illiterate at.

-Zilla

In sheet 1 I have a matrix of
"ShaneDevenshire" wrote in
message ...
Hi Zilla,

Maybe you should start over. What are you asking?
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...
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" wrote in
message
...
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" wrote:

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)

In the sample file, the code checks for a specific column:

If Target.Column = 2 Then

You could change that to a different column


Zilla wrote:
Like I said, Debra D's worksheets presented a VBA
solution and I'm studying that too. How do the VBA
macros run for certain cells only?

"Zilla" wrote in message
...

Ok Shane, here goes

Say I have sheet 1 that has route numbers (Rt#) tabulated
against the actual orig (O) and dest (D) route, like this

Rt# O D O-D
A B C D
1 1 a b a-b
2 2 c d c-d
.................
24 24 x y x-y

So Rt# 1 represents route a-b, Rt# 2 represents route c-d,
etc, or in general, Rt# n represents O-D route.

Someone suggested creating the D column that has
the formulae...
D1=B1&"-"&C1
D2=B2&"-"&C2
...for each D cell to show the, for example, a-b. So I
created a list with the D col. and called it "FromTo"

In sheet 2, when a user clicks on a cell, I want to present
the user with the "FromTo" drop down list that looks like

a-b
c-d
.....
x-y

If he chooses the "c-d" route, the cell will ultimately contain the
corresponding Rt#, in this case "2".

So, in pseudo-code, Sheet2!A cells may have this formula

Ax=if(Match(FromTo choice, Sheet1!Rt#), Sheet1!Rt#, Error)

Makes a little more sense?

I'm studying Debra D's example now, but it involves VB, which,
like Excel, I'm illiterate at.

-Zilla

In sheet 1 I have a matrix of
"ShaneDevenshire" wrote in
message ...

Hi Zilla,

Maybe you should start over. What are you asking?
--
Thanks,
Shane Devenshire


"Zilla" wrote:


Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...

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" wrote in

message

...

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


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)












--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Zilla,

Debra has suggested a VBA solution because what you are asking would require
VBA in most cases because a cell either contains one item or another, not two
things.

So you can use the MATCH approach as long as you don't mind having the route
number appear in a different cell. Suppose you name the column D cells,
which contain the a-b entries, List. Then in your second sheet where you
want the drop down list, say cell A1, you choose the command Data,
Validation, set Allow to List and then enter the formula =List in Source box.

In another cell enter the formula =OFFSET(Sheet1!$A$1,MATCH(A1,List,0),0)

If that is not acceptable then you must take a VBA approach.
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Like I said, Debra D's worksheets presented a VBA
solution and I'm studying that too. How do the VBA
macros run for certain cells only?

"Zilla" wrote in message
...
Ok Shane, here goes

Say I have sheet 1 that has route numbers (Rt#) tabulated
against the actual orig (O) and dest (D) route, like this

Rt# O D O-D
A B C D
1 1 a b a-b
2 2 c d c-d
.................
24 24 x y x-y

So Rt# 1 represents route a-b, Rt# 2 represents route c-d,
etc, or in general, Rt# n represents O-D route.

Someone suggested creating the D column that has
the formulae...
D1=B1&"-"&C1
D2=B2&"-"&C2
...for each D cell to show the, for example, a-b. So I
created a list with the D col. and called it "FromTo"

In sheet 2, when a user clicks on a cell, I want to present
the user with the "FromTo" drop down list that looks like

a-b
c-d
.....
x-y

If he chooses the "c-d" route, the cell will ultimately contain the
corresponding Rt#, in this case "2".

So, in pseudo-code, Sheet2!A cells may have this formula

Ax=if(Match(FromTo choice, Sheet1!Rt#), Sheet1!Rt#, Error)

Makes a little more sense?

I'm studying Debra D's example now, but it involves VB, which,
like Excel, I'm illiterate at.

-Zilla

In sheet 1 I have a matrix of
"ShaneDevenshire" wrote in
message ...
Hi Zilla,

Maybe you should start over. What are you asking?
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...
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" wrote in
message
...
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" wrote:

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)

Ok, I'll try your approach. I'm also learning Debra's code. :)

"ShaneDevenshire" wrote in
message ...
Hi Zilla,

Debra has suggested a VBA solution because what you are asking would
require
VBA in most cases because a cell either contains one item or another, not
two
things.

So you can use the MATCH approach as long as you don't mind having the
route
number appear in a different cell. Suppose you name the column D cells,
which contain the a-b entries, List. Then in your second sheet where you
want the drop down list, say cell A1, you choose the command Data,
Validation, set Allow to List and then enter the formula =List in Source
box.

In another cell enter the formula =OFFSET(Sheet1!$A$1,MATCH(A1,List,0),0)

If that is not acceptable then you must take a VBA approach.
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Like I said, Debra D's worksheets presented a VBA
solution and I'm studying that too. How do the VBA
macros run for certain cells only?

"Zilla" wrote in message
...
Ok Shane, here goes

Say I have sheet 1 that has route numbers (Rt#) tabulated
against the actual orig (O) and dest (D) route, like this

Rt# O D O-D
A B C D
1 1 a b a-b
2 2 c d c-d
.................
24 24 x y x-y

So Rt# 1 represents route a-b, Rt# 2 represents route c-d,
etc, or in general, Rt# n represents O-D route.

Someone suggested creating the D column that has
the formulae...
D1=B1&"-"&C1
D2=B2&"-"&C2
...for each D cell to show the, for example, a-b. So I
created a list with the D col. and called it "FromTo"

In sheet 2, when a user clicks on a cell, I want to present
the user with the "FromTo" drop down list that looks like

a-b
c-d
.....
x-y

If he chooses the "c-d" route, the cell will ultimately contain the
corresponding Rt#, in this case "2".

So, in pseudo-code, Sheet2!A cells may have this formula

Ax=if(Match(FromTo choice, Sheet1!Rt#), Sheet1!Rt#, Error)

Makes a little more sense?

I'm studying Debra D's example now, but it involves VB, which,
like Excel, I'm illiterate at.

-Zilla

In sheet 1 I have a matrix of
"ShaneDevenshire" wrote in
message ...
Hi Zilla,

Maybe you should start over. What are you asking?
--
Thanks,
Shane Devenshire


"Zilla" wrote:

Also, I want to use this drop down list that has the
from-to on another sheet.

"Zilla" wrote in message news:...
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" wrote
in
message
...
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" wrote:

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)