Validation list - limit entry to left characters

SewerUrchin · Sep 25, 2006

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 · Sep 25, 2006

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 · Sep 27, 2006

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 · Sep 27, 2006

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 · Sep 28, 2006

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 · Nov 21, 2006

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

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 · Dec 12, 2010

Peter_SSs said:
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.PrivateSub Worksheet_Change(ByVal TargetAs Range)
****' This code extracts just the left 'Code' if the changed
****' cell is D5
****If Target.Address<> "$D$5"ThenExitSub
****Application.EnableEvents =False
****Target.Value = Left(Target.Value, InStr(1, Target.Value, " ") - 1)
****Application.EnableEvents =TrueEndSubPrivateSub Worksheet_SelectionChange(ByVal TargetAs 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
****EndIfEndSub

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 · Apr 10, 2012

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 · Jun 6, 2012

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

Validation list - limit entry to left characters

SewerUrchin

New Member

Excel Facts

Oaktree

MrExcel MVP

SewerUrchin

New Member

Oaktree

MrExcel MVP

SewerUrchin

New Member

Peter_SSs

MrExcel MVP, Moderator

ajm

Well-known Member

belebala

New Member

tmanery

New Member

Similar threads

Forum statistics

Share this page

Validation list - limit entry to left characters

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP, Moderator

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock