Hi

Is it possible to have a cell value adjust depending what is typed into it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

A cell can't contain both a value and a formula, which is what you're
proposing.

One thing you could do is, for example, enter values in column A and have
column B do vlookups on the value in column A.
--
Brevity is the soul of wit.


"Sandy" wrote:

Hi

Is it possible to have a cell value adjust depending what is typed into it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

You can have another cell change according to what you enter in to it, but
the cell itself cannot change except by using VB code. A value entered into
a cell can only change another cell, not itself. Using VB code like the
Worksheet_Change event is definitely possible, but it brings it's own
problems such as no audit trail of what you originally entered. I personally
wouldn't recommend it,
Regards,
Alan.
"Sandy" wrote in message
...
Hi

Is it possible to have a cell value adjust depending what is typed into
it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

Note this is Case sensitive.........but can be modified not to be if
needed......

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Returns specified result for given value entered in A1
' by Chuck, CABGx3
If Not Target.Range("a1") Is Nothing Then
Select Case Range("A1").Value
Case "A"
Range("A1").Value = "Arnold"
Case "B"
Range("A1").Value = "Brendan"
Case "C"
Range("A1").Value = "Charlie"
Case Else
Range("A1") = ""
End Select
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3




"Sandy" wrote:

Hi

Is it possible to have a cell value adjust depending what is typed into it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

Taking your question literally, I would suggest using event code since a formula
and a typed entry cannot exist in the same cell.

What are you trying to achieve?

Do you have more than 3 choices or is A, B and C just an example?

There are other methods involving a formula and a helper cell to return the
result based on the entry in the cell.


Gord Dibben MS Excel MVP

On Wed, 29 Nov 2006 19:13:32 -0000, "Sandy"
wrote:

Hi

Is it possible to have a cell value adjust depending what is typed into it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

Being re-sent, apparently didn't go out the first time

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Returns specific value for given case-sensitive entry in A1
' can be modified to NOT be case-sensitive if desired
' by Chuck, CABGx3
If Not Target.Range("a1") Is Nothing Then
Select Case Range("A1").Value
Case "A"
Range("A1").Value = "Arnold"
Case "B"
Range("A1").Value = "Brendan"
Case "C"
Range("A1").Value = "Charlie"
Case Else
Range("A1") = ""
End Select
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3



"Sandy" wrote:

Hi

Is it possible to have a cell value adjust depending what is typed into it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

Chuck,
You're first post was sent, I can see it. You may be experiencing what
happened to me and apparently quite a few others some time ago. If you go
'Find' the post is there, but it doesn't show on the listings for some
reason. I use Outlook Express but I think it happens on Outlook too. I have
no idea why, I asked questions at the time but got no satisfactory answer,
Regards,
Alan.

"CLR" wrote in message
...
Being re-sent, apparently didn't go out the first time

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Returns specific value for given case-sensitive entry in A1
' can be modified to NOT be case-sensitive if desired
' by Chuck, CABGx3
If Not Target.Range("a1") Is Nothing Then
Select Case Range("A1").Value
Case "A"
Range("A1").Value = "Arnold"
Case "B"
Range("A1").Value = "Brendan"
Case "C"
Range("A1").Value = "Charlie"
Case Else
Range("A1") = ""
End Select
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3



"Sandy" wrote:

Hi

Is it possible to have a cell value adjust depending what is typed into
it?
E.g. I want to be able to type A or B or C and have the value of the same
cell change to Arnold or Brendan or Charlie respectively.

Sandy

Thanks Alan..........I think there was a mix-up in the newsgroup the last
day or so.......lotsa missing posts, but now I think they are back or coming
back anyway.........appreciate the info.........

Vaya con Dios,
Chuck, CABGx3


"Alan" wrote in message
...
Chuck,
You're first post was sent, I can see it. You may be experiencing what
happened to me and apparently quite a few others some time ago. If you go
'Find' the post is there, but it doesn't show on the listings for some
reason. I use Outlook Express but I think it happens on Outlook too. I
have
no idea why, I asked questions at the time but got no satisfactory answer,
Regards,
Alan.

"CLR" wrote in message
...
Being re-sent, apparently didn't go out the first time

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' Returns specific value for given case-sensitive entry in A1
' can be modified to NOT be case-sensitive if desired
' by Chuck, CABGx3
If Not Target.Range("a1") Is Nothing Then
Select Case Range("A1").Value
Case "A"
Range("A1").Value = "Arnold"
Case "B"
Range("A1").Value = "Brendan"
Case "C"
Range("A1").Value = "Charlie"
Case Else
Range("A1") = ""
End Select
End If
End Sub

hth
Vaya con Dios,
Chuck, CABGx3



"Sandy" wrote:

Hi

Is it possible to have a cell value adjust depending what is typed into
it?
E.g. I want to be able to type A or B or C and have the value of the
same
cell change to Arnold or Brendan or Charlie respectively.

Sandy