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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
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
7,984
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
46,797
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.<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
 

ajm

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

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:

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,448
Members
410,611
Latest member
JB_101920
Top