Validation list - limit entry to left characters

[SewerUrchin]

Hi folks. Sorry to address a facet of something that seems to have been talked about many times before, but I still can't find a straight forward answer for my problem.

I have a spreadsheet that I'm wanting to provide a validated list of values for, but I only want particular codes to be entered into the cell. So in column IQ I have the codes I'd like to have selected and placed into the cell when the user makes their selection. In column IR, I have the description of the code that the user will select. The codes by themselves may not be intuitive, so I need to provide the description with the code. I know I cannot select two columns for my validation list. So I must either do some VB coding, or I must combine the code and description into one column and somehow trim off the first three characters as the code that will be placed as the value selection.

First, is it possible to use LEFT as a function in the validation formula for the validation list? If it is, how do you do this without the "LEFT" being used when the list is being displayed to the user? If this is not possible, is it then just as easy to leave the data in separte columns while writing VB code to make the correct presentation to the end user? Would a macro that fires right after the user makes their selection but before the value is written to the cell be a possiblity, if used in conjunction with the LEFT fucntion?

I'm an extremely novice vb coder so any suggestions for an easy way to do this will be very greatly appreciated. And my apologies if this has been asked before. If it has, I couldn't find it using any serch methods i am aware of and I looked for more than an hour.

Many thanks!

Jeff

 

[Oaktree]

In the spirit of Alice in Wonderland ("Start at the beginning, and when you get to the end, stop!"):

First, is it possible to use LEFT as a function in the validation formula for the validation list?

Yes, it's possible. To limit the first 4 characters of A1 to "ABCD", data validate A1 as custom Formula =LEFT(A1,4)="ABCD"

 

[SewerUrchin]

Thanks Oaktree:

But I'm looking to use LEFT with the validation list function. I have a column with data that appears like this: "TST = Testing" So the challenge is to show the entire string but only have "TST" entered into the cell when the user makes a selection.

Do you know if this can be done using a validation list instead of custom, because custom doesn't present a list of choices .

Thanks!

 

[Oaktree]

So I'm clear, you want to have the user enter TST (or some other abbreviated form of the description) into the cell and have "TST = Testing" (or whatever the full mapping is) displayed? This is not possible without a macro. Or, rather, it's not possible in one cell without a macro. You could use a vlookup function to, when you enter TST in one cell, show "= testing" in the cell to the right to create the same effect, which may be easier than going the macro route.

 

[SewerUrchin]

Thanks again Oaktree:

What you provided definitely answered my question - that using the validation list function will not work if I'm wanting to store less information than what is presented when the user clicks the dropdown.

The issue isn't insanely important. It was just a thing that was niggling at me because I thought there should be an easier way to do this, besides having to resort to VB code.

I did develop a work around, clunky that it is, because I didn't think the application was worth investing more time in. I've placed a comment that contains a description of what each code equals every ten rows. While this is unelegant, it does give the user something to look at if they need it and it doesn't require the use of VB to do it.

Thanks again for sharing your knowledge!

 

[Peter_SSs]

Jeff

Don't know if you are still interested in this, but it came up (if I have understood your issue correctly) in a project I have been working on recently.

My Data Validation cell is D5, which is quite a narrow column.
The codes I want to appear in D5 are listed in G2:G4
The Descriptions of those codes are in H2:H4
The codes & descriptions are concatenated in I2:I4 with I2 (copied down):
=G2&" "&H2

1. Data Validation in D5 is 'List' with source: =$I$2:$I$4
2. Worksheet_Change event code extracts just the 'Code' from the Data Validation Choice.
3. Worksheet_SelectionChange event code tempoariliy expand column D when D5 is selected so that the user can view the Code & Description in the Data Validation drop-down.

Private Sub Worksheet_Change(ByVal Target As Range)
  ' This code extracts just the left 'Code' if the changed ' cell is D5
  If Target.Address = "$D$5" Then
    Application.EnableEvents = False
    Target.Value = Left(Target.Value, InStr(1, Target.Value, " ") - 1)
    Application.EnableEvents = True
  End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' This code tempoarily expands column D when D5 is selected so that ' the Data validation list of Code & Description can be easily read.
  If Target.Address = "$D$5" Then
    Range("$D$5").ColumnWidth = 35
  Else
    Range("$D$5").ColumnWidth = 5
  End If
End Sub

20 12 05.xlsm
	C	D	E	F	G	H	I
1					Code	Description	Code & Description
2					D1	Work related car expenses	D1 Work related car expenses
3					D20	Other expenses	D20 Other expenses
4					D3	Donations	D3 Donations
5		D20
6
DV

Cell Formulas
Range	Formula
I2:I4	I2	=G2&" "&H2

 

[ajm]

Jeff

Don't know if you are still interested in this, but it came up (if I have understood your issue correctly) in a project I have been working on recently.

My Data Validation cell is D5, which is quite a narrow column.
The codes I want to appear in D5 are listed in G2:G4
The Descriptions of those codes are in H2:H4
The codes & descriptions are concatenated in I2:I4 with I2 (copied down):
=G2&" "&H2

1. Data Validation in D5 is 'List' with source: =$I$2:$I$4
2. Worksheet_Change event code extracts just the 'Code' from the Data Validation Choice.
3. Worksheet_SelectionChange event code tempoariliy expand column D when D5 is selected so that the user can view the Code & Description in the Data Validation drop-down.<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)
****<SPAN style="color:#007F00">' This code extracts just the left 'Code' if the changed</SPAN>
****<SPAN style="color:#007F00">' cell is D5</SPAN>
****<SPAN style="color:#00007F">If</SPAN> Target.Address<> "$D$5"<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
****Application.EnableEvents =<SPAN style="color:#00007F">False</SPAN>
****Target.Value = Left(Target.Value, InStr(1, Target.Value, " ") - 1)
****Application.EnableEvents =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)
****<SPAN style="color:#007F00">' This code tempoarily expands column D when D5 is selected so that</SPAN>
****<SPAN style="color:#007F00">' the Data validation list of Code & Description can be easily read.</SPAN>
****<SPAN style="color:#00007F">If</SPAN> Target.Address = "$D$5"<SPAN style="color:#00007F">Then</SPAN>
********Range("$D$5").ColumnWidth = 35
****<SPAN style="color:#00007F">Else</SPAN>
********Range("$D$5").ColumnWidth = 5
****<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Wide Data Validation.xls
	C	D	E	F	G	H	I	J
1					Code	Description	Code & Description
2					D1	Work related car expenses	D1 Work related car expenses
3					D20	Other expenses	D20 Other expenses
4					D3	Donations	D3 Donations
5		D20
6
Sheet1

Peter, wonder if you are still watching this one... i need to change the target value to a whole column, not just one cell and having no luck?

 

[belebala]

wonder how to change the target value to a whole column, not just one cell. and I received an error if I change the "Code" column from string to number. anyone knows how to fix this?

 

[tmanery]

For checking to see if the whole column is selected try this:

If Selection.Column = 4 Then' Change to the appropriate column number
If Columns("D:D").ColumnWidth <> 35 Then Columns("D:D").ColumnWidth =35
Else
If Columns("D:D").ColumnWidth <> 5 Then Columns("D:D").ColumnWidth = 5
End If
End Sub