is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete

Use two cells:

The first cell will have a data validation pull-down to enter the text.
The second cell will have a VLOOKUP() formula to lookup the correct number.
--
Gary''s Student - gsnu2007j


"Pete" wrote:

is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete

On 24 Jul, 18:11, Gary''s Student
wrote:
Use two cells:

The first cell will have a data validation pull-down to enter the text.
The second cell will have a VLOOKUP() formula to lookup the correct number.
--
Gary''s Student - gsnu2007j



"Pete" wrote:
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete- Hide quoted text -

- Show quoted text -

Sheets is already designed, I only have one Cell to work with

Just use a LEFT(Cell Ref,1) to get the number
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Pete" wrote:

On 24 Jul, 18:11, Gary''s Student
wrote:
Use two cells:

The first cell will have a data validation pull-down to enter the text.
The second cell will have a VLOOKUP() formula to lookup the correct number.
--
Gary''s Student - gsnu2007j



"Pete" wrote:
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete- Hide quoted text -

- Show quoted text -

Sheets is already designed, I only have one Cell to work with

On 24 Jul, 18:22, M Kan <tipsoftheweek at gmail dot com wrote:
Just use a LEFT(Cell Ref,1) to get the number
--
Tips for Excel, Word, PowerPoint and Other Applicationshttp://www.kan.org/tips



"Pete" wrote:
On 24 Jul, 18:11, Gary''s Student
wrote:
Use two cells:

The first cell will have a data validation pull-down to enter the text.
The second cell will have a VLOOKUP() formula to lookup the correct number.
--
Gary''s Student - gsnu2007j

"Pete" wrote:
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete- Hide quoted text -

- Show quoted text -

Sheets is already designed, I only have one Cell to work with- Hide quoted text -

- Show quoted text -

I need to use a drop down list, I don't think I can use the Left(Cell
Ref,1) command.

You could use the Combo Box form tool. That would let you present a list but
output a number. Other than that, I'm not sure how to achieve what you want.

http://www.kan.org/tips/excel_forms_tools.php
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Pete" wrote:

On 24 Jul, 18:22, M Kan <tipsoftheweek at gmail dot com wrote:
Just use a LEFT(Cell Ref,1) to get the number
--
Tips for Excel, Word, PowerPoint and Other Applicationshttp://www.kan.org/tips



"Pete" wrote:
On 24 Jul, 18:11, Gary''s Student
wrote:
Use two cells:

The first cell will have a data validation pull-down to enter the text.
The second cell will have a VLOOKUP() formula to lookup the correct number.
--
Gary''s Student - gsnu2007j

"Pete" wrote:
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete- Hide quoted text -

- Show quoted text -

Sheets is already designed, I only have one Cell to work with- Hide quoted text -

- Show quoted text -

I need to use a drop down list, I don't think I can use the Left(Cell
Ref,1) command.

To enter the number in the same cell use this example sheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1") 'edit to suit the DV cell(s)
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If
vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit
nums = Array(1, 2, 3, 4, 5, 6) 'add more numbers
For Each rr In r
ival = 0
For i = LBound(vals) To UBound(vals)
If UCase(rr.Value) = vals(i) Then
ival = nums(i)
End If
Next
If ival 0 Then
rr.Value = ival
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Thu, 24 Jul 2008 09:53:16 -0700 (PDT), Pete
wrote:

is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete

On 24 Jul, 20:22, Gord Dibben <gorddibbATshawDOTca wrote:
To enter the number in the same cell use this example sheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1") *'edit to suit the DV cell(s)
If Intersect(Target, r) Is Nothing Then
* * Exit Sub
End If
vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit
nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers
For Each rr In r
* * ival = 0
* * For i = LBound(vals) To UBound(vals)
* * * * If UCase(rr.Value) = vals(i) Then
* * * * * * ival = nums(i)
* * * * End If
* * Next
* * If ival 0 Then
* * rr.Value = ival
* * End If
Next
End Sub

Gord Dibben *MS Excel MVP

On Thu, 24 Jul 2008 09:53:16 -0700 (PDT), Pete



wrote:
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete- Hide quoted text -

- Show quoted text -

Gord, you are THE man, perfect solution, thank you

On 24 Jul, 20:22, Gord Dibben <gorddibbATshawDOTca wrote:
To enter the number in the same cell use this example sheet event code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("A1") *'edit to suit the DV cell(s)
If Intersect(Target, r) Is Nothing Then
* * Exit Sub
End If
vals = Array("A", "B", "C", "D", "E", "F") 'edit to suit
nums = Array(1, 2, 3, 4, 5, 6) *'add more numbers
For Each rr In r
* * ival = 0
* * For i = LBound(vals) To UBound(vals)
* * * * If UCase(rr.Value) = vals(i) Then
* * * * * * ival = nums(i)
* * * * End If
* * Next
* * If ival 0 Then
* * rr.Value = ival
* * End If
Next
End Sub

Gord Dibben *MS Excel MVP

On Thu, 24 Jul 2008 09:53:16 -0700 (PDT), Pete



wrote:
is it possible to choose an item from a dropdown list which will enter
a different value other than that chosen. Why I want to do this is as
follows:

We have 20 steps in our Process and each one is assigned a number e.g

1 = Forming
2 = Drying
3 = Paper
4 = Cutter

etc etc

I would like to display the Text in a Drop down for that Cell but
enter the corresponding Number into the Cell. If I chose Drying I
would like to see the number 2 appear in the Cell, or if I chose
Cutter I would like the Number 4 to be entered in the Cell.

Thanks

Pete- Hide quoted text -

- Show quoted text -

Gord,

A similar problem, I would like to enter a value in the C11 and
convert it to another after the Value has been entered. E.g If I enter
100 in C11 I would like to Divide it by the Value I have in C17 under
the Change Event Procedure. I would need to do this in the full Range
e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17

CAn yuo show me how to adapt your previous code to do this too?

Thanks

Pete

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C11,E11,G11,I11,K11" 'add cells to suit
Dim cell As Range
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < "" Then
.Value = .Value * .Offset(6, 0).Value
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


Gord

On Fri, 25 Jul 2008 11:58:53 -0700 (PDT), Pete
wrote:

A similar problem, I would like to enter a value in the C11 and
convert it to another after the Value has been entered. E.g If I enter
100 in C11 I would like to Divide it by the Value I have in C17 under
the Change Event Procedure. I would need to do this in the full Range
e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17

CAn yuo show me how to adapt your previous code to do this too?

Thanks

Pete

On 26 Jul, 00:15, Gord Dibben <gorddibbATshawDOTca wrote:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C11,E11,G11,I11,K11" * * 'add cells to suit
Dim cell As Range
* * On Error GoTo ws_exit
* * Application.EnableEvents = False
* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * If .Value < "" Then
* * * * * * .Value = .Value * .Offset(6, 0).Value
* * * * * * End If
* * * * End With
* * End If
ws_exit:
* * Application.EnableEvents = True
End Sub

Gord

On Fri, 25 Jul 2008 11:58:53 -0700 (PDT), Pete
wrote:



A similar problem, I would like to enter a value in the C11 and
convert it to another after the Value has been entered. E.g If I enter
100 in C11 I would like to Divide it by the Value I have in C17 under
the Change Event Procedure. I would need to do this in the full Range
e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17

CAn yuo show me how to adapt your previous code to do this too?

Thanks

Pete- Hide quoted text -

- Show quoted text -

Thanks Gord, I will give it a try.

I now have pieces of code in the Change Sub and they seem to affect
each other. Can this piece of code you've just done for me work along
side the earlier one you did?

Pete

On 26 Jul, 21:13, Pete wrote:
On 26 Jul, 00:15, Gord Dibben <gorddibbATshawDOTca wrote:





Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C11,E11,G11,I11,K11" * * 'add cells to suit
Dim cell As Range
* * On Error GoTo ws_exit
* * Application.EnableEvents = False
* * If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
* * * * With Target
* * * * If .Value < "" Then
* * * * * * .Value = .Value * .Offset(6, 0).Value
* * * * * * End If
* * * * End With
* * End If
ws_exit:
* * Application.EnableEvents = True
End Sub

Gord

On Fri, 25 Jul 2008 11:58:53 -0700 (PDT), Pete
wrote:

A similar problem, I would like to enter a value in the C11 and
convert it to another after the Value has been entered. E.g If I enter
100 in C11 I would like to Divide it by the Value I have in C17 under
the Change Event Procedure. I would need to do this in the full Range
e.g Divide E11 by E17 and G11 by G17 etc etc. upto AK11/AK17

CAn yuo show me how to adapt your previous code to do this too?

Thanks

Pete- Hide quoted text -

- Show quoted text -

Thanks Gord, I will give it a try.

I now have pieces of code in the Change Sub and they seem to affect
each other. Can this piece of code you've just done for me work along
side the earlier one you did?

Pete- Hide quoted text -

- Show quoted text -

Gord, perfect again exacly what I want. If I post all the code I have
in the Change Event sub, could you tidy it up for me so it works
properly. I can get the ones you have done for me to work seperately,
but when they are both in only the first one works. I also have Chip
Pearson's Quick Time Entry code in there to and I could od with all 3
working.

thanks in advance

Pete