ENTER ONE VALUE, RETURN ANOTHER
 

HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the keyboard
get Excel to return a value into cell C2 (depending on the letter entered in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive 12 in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah

Hi

you need to use worksheet_change code
e.g.
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
Application.EnableEvents = False
If Target.Address = "$C$2" And Target.Value < "" Then
Select Case UCase(Target.Value)
Case "A"
Target.Value = 12
Case "B"
Target.Value = 10
Case "C"
Target.Value = 8
Case "D"
Target.Value = 6
Case "E"
Target.Value = 4
Case "F"
Target.Value = 2
Case "U"
Target.Value = 0
End Select
End If
err_handler:
Application.EnableEvents = True
End Sub
---
to use this code, right mouse click on the sheet tab of the sheet you want
it to run on
choose view code
copy & paste the above code in the right hand side of the screen
use ALT & F11
to switch back to your workbook and test

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Sarah_Lecturer" wrote in message
...
HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the
keyboard
get Excel to return a value into cell C2 (depending on the letter entered
in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive 12
in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah

So, If I wanted to expand from C2 could I just add C2:E2 for example?

Thanks so much for your help - perfect so far....

Thanks again

Sarah

"JulieD" wrote:

Hi

you need to use worksheet_change code
e.g.
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
Application.EnableEvents = False
If Target.Address = "$C$2" And Target.Value < "" Then
Select Case UCase(Target.Value)
Case "A"
Target.Value = 12
Case "B"
Target.Value = 10
Case "C"
Target.Value = 8
Case "D"
Target.Value = 6
Case "E"
Target.Value = 4
Case "F"
Target.Value = 2
Case "U"
Target.Value = 0
End Select
End If
err_handler:
Application.EnableEvents = True
End Sub
---
to use this code, right mouse click on the sheet tab of the sheet you want
it to run on
choose view code
copy & paste the above code in the right hand side of the screen
use ALT & F11
to switch back to your workbook and test

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Sarah_Lecturer" wrote in message
...
HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the
keyboard
get Excel to return a value into cell C2 (depending on the letter entered
in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive 12
in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah

Duh!

I am so thick - just realised if I remove the if statement it will perform
for the entire spreadsheet - Thanks

Sarah

"JulieD" wrote:

Hi

you need to use worksheet_change code
e.g.
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
Application.EnableEvents = False
If Target.Address = "$C$2" And Target.Value < "" Then
Select Case UCase(Target.Value)
Case "A"
Target.Value = 12
Case "B"
Target.Value = 10
Case "C"
Target.Value = 8
Case "D"
Target.Value = 6
Case "E"
Target.Value = 4
Case "F"
Target.Value = 2
Case "U"
Target.Value = 0
End Select
End If
err_handler:
Application.EnableEvents = True
End Sub
---
to use this code, right mouse click on the sheet tab of the sheet you want
it to run on
choose view code
copy & paste the above code in the right hand side of the screen
use ALT & F11
to switch back to your workbook and test

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Sarah_Lecturer" wrote in message
...
HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the
keyboard
get Excel to return a value into cell C2 (depending on the letter entered
in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive 12
in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah

glad it's solved :)

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Sarah_Lecturer" wrote in message
...
Duh!

I am so thick - just realised if I remove the if statement it will perform
for the entire spreadsheet - Thanks

Sarah

"JulieD" wrote:

Hi

you need to use worksheet_change code
e.g.
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
Application.EnableEvents = False
If Target.Address = "$C$2" And Target.Value < "" Then
Select Case UCase(Target.Value)
Case "A"
Target.Value = 12
Case "B"
Target.Value = 10
Case "C"
Target.Value = 8
Case "D"
Target.Value = 6
Case "E"
Target.Value = 4
Case "F"
Target.Value = 2
Case "U"
Target.Value = 0
End Select
End If
err_handler:
Application.EnableEvents = True
End Sub
---
to use this code, right mouse click on the sheet tab of the sheet you
want
it to run on
choose view code
copy & paste the above code in the right hand side of the screen
use ALT & F11
to switch back to your workbook and test

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Sarah_Lecturer" wrote in
message
...
HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the
keyboard
get Excel to return a value into cell C2 (depending on the letter
entered
in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive
12
in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah

Set up your lookup table in col b & c

a 11
x 111
c 11111
u 12


right click sheet tabinsert thismodify to suitSAVE
Now if you type in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$C$2" Then Exit Sub
Application.EnableEvents = False
On Error Resume Next
Target = Columns(2).Find(Target).Offset(, 1)
Application.EnableEvents = True
End Sub
--
Don Guillett
SalesAid Software

"Sarah_Lecturer" wrote in message
...
HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the
keyboard
get Excel to return a value into cell C2 (depending on the letter entered
in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive 12
in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah

If you are using those 7 numbers in your table, another option might be:

Select Case UCase(Target.Value)
Case "A" To "F", "U"
Target.Value = 62674966 Mod (Asc(Target.Value) - 48)
Case Else
' Do something else
End Select
'...etc

HTH
--
Dana DeLouis
Win XP & Office 2003


"Sarah_Lecturer" wrote in message
...
Duh!

I am so thick - just realised if I remove the if statement it will perform
for the entire spreadsheet - Thanks

Sarah

"JulieD" wrote:

Hi

you need to use worksheet_change code
e.g.
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo err_handler
Application.EnableEvents = False
If Target.Address = "$C$2" And Target.Value < "" Then
Select Case UCase(Target.Value)
Case "A"
Target.Value = 12
Case "B"
Target.Value = 10
Case "C"
Target.Value = 8
Case "D"
Target.Value = 6
Case "E"
Target.Value = 4
Case "F"
Target.Value = 2
Case "U"
Target.Value = 0
End Select
End If
err_handler:
Application.EnableEvents = True
End Sub
---
to use this code, right mouse click on the sheet tab of the sheet you
want
it to run on
choose view code
copy & paste the above code in the right hand side of the screen
use ALT & F11
to switch back to your workbook and test

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Sarah_Lecturer" wrote in
message
...
HELP!

I want to enter a letter in cell C2 and upon pressing ENTER on the
keyboard
get Excel to return a value into cell C2 (depending on the letter
entered
in).

A = 12
B = 10
C = 8
D = 6
E = 4
F = 2
U = 0

I have tried using a VLOOKUP but because I want to enter A and receive
12
in
the same cell I keep ending up with circular references - help!!!

Thanks

Sarah