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)

Debra, I'll be using your VBA code. In it, you have the
macro running on col 2, and I was able to run it on a
different "test" col. just to make sure I understand it.

Now how do I run it on multiple columns? Also, say
I'm able to run it on 10 cols today, but tomorrow I add
5 more cols. Do I need to modify the code to manually
add these cols, or can it be written to adapt to cols I
add?

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

If all the data validation cells use the same list, you could modify the
code to check for data validation cells, instead of a specific column.
For example:

'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range

On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Value = "" Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
GoTo exitHandler
Else
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub
'========================

Zilla wrote:
Debra, I'll be using your VBA code. In it, you have the
macro running on col 2, and I was able to run it on a
different "test" col. just to make sure I understand it.

Now how do I run it on multiple columns? Also, say
I'm able to run it on 10 cols today, but tomorrow I add
5 more cols. Do I need to modify the code to manually
add these cols, or can it be written to adapt to cols I
add?

"Zilla" wrote in message
...

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)















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

Thanks. Can I do 2 conditions in the if statement, like
If Target.Cells.Count 1 && If Target.Cells.Count < 24...

"Debra Dalgleish" wrote in message
...
If all the data validation cells use the same list, you could modify the
code to check for data validation cells, instead of a specific column.
For example:

'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range

On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Value = "" Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
GoTo exitHandler
Else
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub
'========================

Zilla wrote:
Debra, I'll be using your VBA code. In it, you have the
macro running on col 2, and I was able to run it on a
different "test" col. just to make sure I understand it.

Now how do I run it on multiple columns? Also, say
I'm able to run it on 10 cols today, but tomorrow I add
5 more cols. Do I need to modify the code to manually
add these cols, or can it be written to adapt to cols I
add?

"Zilla" wrote in message
...

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)















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

If you want to check the column number, you could use something like:

If Target.Column 1 And Target.Column <24 Then


Zilla wrote:
Thanks. Can I do 2 conditions in the if statement, like
If Target.Cells.Count 1 && If Target.Cells.Count < 24...

"Debra Dalgleish" wrote in message
...

If all the data validation cells use the same list, you could modify the
code to check for data validation cells, instead of a specific column.
For example:

'========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range

On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Value = "" Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
GoTo exitHandler
Else
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub
'========================

Zilla wrote:

Debra, I'll be using your VBA code. In it, you have the
macro running on col 2, and I was able to run it on a
different "test" col. just to make sure I understand it.

Now how do I run it on multiple columns? Also, say
I'm able to run it on 10 cols today, but tomorrow I add
5 more cols. Do I need to modify the code to manually
add these cols, or can it be written to adapt to cols I
add?

"Zilla" wrote in message
...


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)








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






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