Validation list - limit entry to left characters

SewerUrchin

New Member
Joined
Jan 25, 2005
Messages
24
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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

New Member
Joined
Jan 25, 2005
Messages
24
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,015
Office Version
  1. 365
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

New Member
Joined
Jan 25, 2005
Messages
24

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,510
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
CDEFGHI
1CodeDescriptionCode & Description
2D1Work related car expensesD1 Work related car expenses
3D20Other expensesD20 Other expenses
4D3DonationsD3 Donations
5D20
6
DV
Cell Formulas
RangeFormula
I2:I4I2=G2&" "&H2
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,854

ADVERTISEMENT

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
CDEFGHIJ
1CodeDescriptionCode & Description
2D1Work related car expensesD1 Work related car expenses
3D20Other expensesD20 Other expenses
4D3DonationsD3 Donations
5D20
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

New Member
Joined
Apr 10, 2012
Messages
6
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

New Member
Joined
Jun 6, 2012
Messages
1
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
 
Last edited:

Forum statistics

Threads
1,141,011
Messages
5,703,728
Members
421,312
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top