I want the value from a selection list to be different from the display name of that value in the list.

[helpneeded2]

I'm not sure if there is a way to do this in Excel, but I want to have the ability to select from a list of combined names and id#'s, and only display the id#.

In example:

I am creating a log form, and Column A on my sheet is titled "ID #". The data for this will come from one of two sources: Source #1 would be data from a list on another worksheet on the same spreadsheet, and Source #2 would just be an ID # that the user types in. I was planning to just use the validation list option with error checking disabled, however when the data is selected from the Source #1, I want it to appear in the validation list as: "123456 (SMITH, Micheal)" however I want it to be recorded only as the number "123456".

Is there any ability to do this for all my rows in Column A?

Perhaps under conditional formatting there is a way to accept on the first 6 characters of any data entered there and convert them to numbers?

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshot (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[helpneeded2]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshot (not pictures) of your two sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

There is really nothing to display, so I don't think this would be helpful and wouldn't add any more context than what I provided. I haven't created a sheet yet for doing this function, as there is no point in me doing it, if I can't have this functionality. Column A of the sheet would have a heading called "ID#". The rest of column A would just be cells that you click on that would have a validation list selection.

As for my source data, it actually comes from a single column in a data sheet that displays the value as I indicated:

i.e.:
123456 (SMITH, Micheal)
999999 (JONES, David)

When I select any cell in Column A of my sheet, such as A2, it will show a list of the above data (or will allow you to type in a value). If the value is selected from the list above is for Michael Smith, then the value I want displayed in the column is: 123456

I hope that helps to make things clearer, and I just don't feel it warrants any visual displays.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    Target = Left(Target, 6)
    Application.EnableEvents = True
End Sub

 

[helpneeded2]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    Target = Left(Target, 6)
    Application.EnableEvents = True
End Sub

Thank you for that suggestion. I was hoping there was a non-VBA solution, but feared that it might be the only way. I'm really not proficient with VBA. I will eventually add in some other data columns to the data entry log sheet. How could I modify that code to only apply to cells in a specific range, such as A2:A100?

 

[mumps]

As written, the macro applies to all of column A.

 

[helpneeded2]

As written, the macro applies to all of column A.

Okay, thank you. I'm so used to columns being referenced in letters, that it didn't occur to me that "If Target.Column <> 1 Then Exit Sub" is a reference to column #1.

 

[mumps]

You are very welcome.