Multi-column Data Validation

    Hi Folks, I want to use data validation in a cell that is sourced from a list in another worksheet of the same workbook. The lookup list has two columns, the first column is the persons full name and the second column the persons initials e.g. 'John Smith' (column A), 'JS' (column B).


    When the data validation drop-down is selected I want the user to see simply 'John Smith' or 'John Smith (JS)' and when the desired value is chosen, for brevity only the contents of column B are posted i.e. JS are entered into that cell.


    Any ideas gratefully received.


    cymro

    Re: Multi-column Data Validation


    cymro,


    Since you only want to use Data Validation, I'm going to say it can't be done because either the list does or does not include the name. If the name is in the list, it can be chosen. If you have to change the name to the initials after the name is entered into the cell, you're no longer talking about using just Data Validation, you're talking VBA. Also, I don't think this is a very good strategy because if the list is more than a few names long, you're going to run into people with the same initials.


    A work around is to let the Data Validation control the full name and use a separate column for a lookup of the initials. The full name can be hidden for brevity.


    Jim

    Re: Multi-column Data Validation


    You would be better off with a ListBox on the Worksheet as Validation Lists only allow 1 column.


    1) Name the names and initial columns on the other Worksheet MyNames


    2) On the sheet where the selection list should show, go to View>Toolbars>Control Toolbox and add a ListBox.


    3) Right click on the ListBox and choose Properties and change these settings;


    ColumnCount 2
    BoundColumn 2
    LinkedCell A1 (or the cell the data should show in)
    ListFillRange MyNames


    Now X out and click the Exit Design Mode button on the Control Toolbox Toolbar.

    Re: Multi-column Data Validation


    Thanks guys - I couldn't get the list box solution to work so gave up on that. I think what I need is a VBA solution. The user goes to a cell, a drop-down appears and based on the user selection the cell is actually updated with an abbreviation (in my simple example the person's initials). Drop down shows either 'John Smith (JS)' or 'John Smith (col1) JS (col2)' and the cell ultimately displays 'JS'.


    Anyone like to set me in the right direction please.


    Any help appreciated as ever.


    cymro

    Re: Multi-column Data Validation


    I think the solution is in the worksheet_change event. I cannot get the worksheet_change event to trigger though even though my security is set to low and the procedure is in the target sheet.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login