Only adding last characters from a Data Validation list

[rsrts]

Hi Guys!

I have just added the data validation-function (list) in a cell linked to another sheet. Lets say I have values such as "1234 Horse", "2345 Cow", "3456 Pig" and so on...

Is it it possible to automatically "cut off" the characters following the four digits after it is selected into the cell, while still retaining the whole value in the actual drop down-list? That is, instead of showing "1234 Horse" in a cell after selecting it, it only displays "1234".

//rsrts

 

[lenze]

Hi and welcome to the board!!!
With code?? In the worksheet module (Right Click the sheet tab and choose "view Code"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
Target = Left(Target, 4)
Application.EnableEvents = True
End Sub

Set for your dropdown in A1. Adjust as needed. You can also run on more than one dropdown. Post back your requirements!

lenze

 

[rsrts]

This is very promising, indeed! It gives me the result I am looking for. Thanks for the fast response!

But I guess the only solution to my problem is coding this macro? Then the users I distribute my file to will always get the popup asking them whether to activate macros or not before proceeding? Some colleagues with less computer experience might find it annoying.

I tried to get this macro code to range between cell B7-B20 (<> "$B7:$B20") but I couldn't get it too work. Is this wrong? I am very new to this.

Are there other similar or perhaps better ways to do a list like this without the data validation-function? I guess combo boxes are ruled out since I need to be able to easily copy the values over to another program.