Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    DropDown-select one value, fill another (XP)

    Hi All,

    I am trying to accomplish the following preferably not using VBA:

    Show a (Data Validation) list that shows either:
    - a phrase of 1 or 2 words and an abbreviation; or
    - just the phrase of 1 or 2 words.

    However, I want the cell to be filled with the abbreviation.

    For example, I might show a list as:
    P-Primary
    S-Secondary

    But the cell should be filled with P or S depending on what is selected.

    The format for the list is open. For example, it can be 1 column with entries per above [or something like "Primary (P)"]. It can also be 2 columns with P and Primary, etc. as long as both are shown in the dropdown.

    I'm thinking this is not possible without VBA but don't really have a thought on how to approach this with or without VBA.

    TIA

    Fred

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: DropDown-select one value, fill another (XP)

    Hi Fred,
    I don't think you can do this without VBA. You could use the Worksheet_Change event to alter the cell's value but I think you would have to allow any value to be entered into the cell - is that a problem?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: DropDown-select one value, fill another (XP)

    You can use a control toolbox combobox with 2 columns the bound column would have the abbreviations the 2nd column would have the full list to be displayed in the combobox.

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DropDown-select one value, fill another (XP)

    You could use a combo box from the Control Toolbox with 2 columns. The second column will only be visible when the list is dropped down.
    In the attached example, the combo box is next to the linked cell that contains the selected item. You could move the combo box over the linked cell.
    To edit, display the Control Toolbox and click the Design Mode button (the first button).

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: DropDown-select one value, fill another (XP)

    You could set the Data Validation to List and enter <code>Primary (P),Secondary (S)</code> as the allowed values but clear the warning flag. Then in the worksheet_change event use something like:
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo err_handle
    If Intersect(Target, Me.Columns("K")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Select Case Target.Value
    Case "Primary (P)"
    Target.Value = "P"
    Case "Secondary (S)"
    Target.Value = "S"
    Case Else
    Target.ClearContents
    End Select

    leave:
    Application.EnableEvents = True
    Exit Sub

    err_handle:
    Debug.Print Err.Number
    Resume leave
    End Sub

    </pre>


    Adjust the range to suit.

    That way you can apply it to multiple cells easily.

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: DropDown-select one value, fill another (XP)

    Rory, Hans, Steve,

    Thanks for the really quick answers and the suggestions.

    Since this is the beginning of the project, I'm not sure which way I will go - Worksheet Change or Toolbox Combo.

    Let me say a little more about the project (see attacheed workbook). I am creating a listing of issues in our network. For each issue, there can be one or more causes (up to 7 different causes as of now). So for a given issue, we want to be able to see the Primary and any Secondary causes (hence the P/S). But the columns for causes are very thin so putting a combobox over the cell could be a problem. For now, I've just implemented this as Data Validation with P or S. As we identify more problems, rows get added and the causes have to be identified. This was easily accomplished with the Data Validation using fill. Not sure if that would work with comboboxes.

    Another area where this might come in handy is the Status column (status of eliminating the problem). The cell is to be colored green, red, yellow. I'd like a way to choose the color by dropdown - even if the dropdown has only the word, the cell has to be filled with the selected color. At some point, I will also need to fill the status cell with an arrow showing if things have gotten worse, better, or stayed the same since the last period.

    It might also be nice to provide some summary of the status: eg, Of 6 issues, 3 are green, 1 is yellow, 2 are red. Or finer detail of a 3x3 matrix: color vs change - I'm thinking of a pivot table maybe driven off some hidden columns that translate the entries in the status column into a color and an arrow direction.

    I'm attaching a stripped down version of the spreadsheet. As I said, this is the beginning of the project so some time to think about the best way to go on things. It might turn out to be a manual process to do the above, since we hopefully don't have that many issues.

    Further ideas on any of the above?

    Thanks.

    Fred

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DropDown-select one value, fill another (XP)

    Validation is more suitable for narrow columns and lots of cells. You could specify an input message that explains the choices - or use notes, as you do now.

    You could use a validation dropdown with three single-letter choices in the Status cells, and use Conditional Formatting to change the background color according to the selected letter.
    It's not easy to combine this with an arrow, so perhaps it'd be better to use another column for that, with its own dropdown.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •