Dropdown Complications

[LisaMalaine]

I have a workbook that has a drop down list in it. The options in the dropdown are (1) very good, (2) not good (just example). I have been asked if it is possible to type "1" in the cell where the drop down list exists and for it to replace it to with "(1) very good" in the same cell. But allow the person to also select "(1) very good" from the drop down and not see an option of "1". Any ideas would be wonderful. I have run out of ideas at this point.

Oh one more thing. This is a worksheet that is interactive and has 9 columns with these same drop down options. The idea is to rate each person on the list under each column. At the same time I do not want to slow down the sheet if at all possible.

Thank you!

 

[My Aswer Is This]

Are you saying you want to enter "1" into the cell and then have "Very Good" displayed in the cell
And if you enter "2" into the cell you want "not good" displayed in the cell?

If "yes" then are you willing to use Vba?

If your willing to use Vba this could work and would not require any drop down lists. Just say what column numbers you want this to apply to. And when the user enters "1" into the column cell this would automatically happen.

I would need to know the exact values. Like 1 equals "Very Good" 2 equals "Not Good" etc. etc.
You can make modifications to the script later if you want.

 

[AlphaFrog]

Put Data Validation Dropdown lists in the cells (this example uses cells A2:I2)
Make sure on the DV Error tab that Show Error Alert checkbox is unchecked

Add the code from below in the Worksheet's code module:

• Right-Click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module

  [color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
      [color=darkblue]If[/color] Target.Count = 1 [color=darkblue]Then[/color]
          [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("A2:I2"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
              Application.EnableEvents = [color=darkblue]False[/color]
              [color=darkblue]Select[/color] [color=darkblue]Case[/color] Target.Value
                  [color=darkblue]Case[/color] "1", "(1) Very Good": Target.Value = "(1) Very Good"
                  [color=darkblue]Case[/color] "2", "(2) Not Good": Target.Value = "(2) Not Good"
                  [color=darkblue]Case[/color] Else: Target.Value = ""
              [color=darkblue]End[/color] [color=darkblue]Select[/color]
              Application.EnableEvents = [color=darkblue]True[/color]
          [color=darkblue]End[/color] [color=darkblue]If[/color]
      [color=darkblue]End[/color] [color=darkblue]If[/color]
  End [color=darkblue]Sub[/color]