Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Sarah_Lecturer
 
Posts: n/a
Default 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
  #2   Report Post  
JulieD
 
Posts: n/a
Default

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



  #3   Report Post  
Sarah_Lecturer
 
Posts: n/a
Default

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




  #4   Report Post  
Sarah_Lecturer
 
Posts: n/a
Default

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




  #5   Report Post  
JulieD
 
Posts: n/a
Default

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








  #6   Report Post  
Don Guillett
 
Posts: n/a
Default

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



  #7   Report Post  
Dana DeLouis
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
enter data on 1 sheet and make it enter on next avail row on 2nd s Nadia Excel Discussion (Misc queries) 27 September 9th 05 03:39 PM
Enter one value, Return another Sarah_Lecturer Excel Discussion (Misc queries) 2 April 13th 05 08:35 PM
if the value of a cell in a range is not blank, then return the v. kvail Excel Worksheet Functions 2 April 8th 05 10:07 PM
Hyperlink with Enter key CPA Jammer Excel Discussion (Misc queries) 0 December 22nd 04 04:53 PM
search multiple worksheets for an item and return the Wsheets name Chris Excel Worksheet Functions 16 November 7th 04 01:15 PM


All times are GMT +1. The time now is 11:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"