using only partial of dropdown list

[bluegrassstateworker]

I have a named range of cells (called xcodes) in a single column that
is in the following format:
D123 - Department Text Description Here.
All entries have 4 characters proceeded with a description just as
above. The 4 characters are also unique.
What I would like to do is display the entire string but only the
leftmost 4 characters would be accepted as the value for that cell the
data validation is applied.
I have tried using the formula within data validation as:
=Left(range("xcodes"),4) but that returns an error. Any ideas how to
accomplish this? (Excel 2003 version).

 

[hall.jeff]

I have a named range of cells (called xcodes) in a single column that
is in the following format:
D123 - Department Text Description Here.
All entries have 4 characters proceeded with a description just as
above. The 4 characters are also unique.
What I would like to do is display the entire string but only the
leftmost 4 characters would be accepted as the value for that cell the
data validation is applied.
I have tried using the formula within data validation as:
=Left(range("xcodes"),4) but that returns an error. Any ideas how to
accomplish this? (Excel 2003 version

I myself would just store the left(a1,4) in a separate column and use
that as my data validation...

 

[Dave Peterson]

You could use a combination of your data|validation and a worksheet event that
will truncate the chosen value.

If that's ok...

I used A1 as the cell that used data|validation.

Then rightclick on the worksheet tab that should have this behavior and select
view code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Set myRng = Me.Range("a1")

On Error GoTo ErrHandler:

With Target
If .Cells.Count > 1 Then
Exit Sub
End If

If Intersect(.Cells, myRng) Is Nothing Then
Exit Sub
End If

If Len(.Value) > 4 Then
Application.EnableEvents = False
.Value = Left(.Value, 4)
End If
End With

ErrHandler:
Application.EnableEvents = True
End Sub

 

[kounoike]

I can't get what your validation rule is from your
formula(=Left(range("xcodes"),4)).
I might be wrong but Is that the leftmost 4 characters of the text must be
unique your rule or else?

keiji

 

[bluegrassstateworker]

I can't get what your validation rule is from your
formula(=Left(range("xcodes"),4)).
I might be wrong but Is that the leftmost 4 characters of the text must be
unique your rule or else?

keiji






- Show quoted text -

The users do not really know what D123 stands for, only a select group
of administrative staff really work with these codes. My range
includes perhaps four dozen unique Dcode entries (D123, D120, D713...)
and if I include the description, then the user knows the appropriate
selection. My challenge is that I only have room for the most useful
piece of data which is the 4 characters. What I describe I wish to do
is done quite often in databases where multiple fields are shown in a
dropdown list but only a value in a selected field is actually saved
as the value in the table.

 

[kounoike]

Thank you for an explanation, but i can't still make it clear what you want
to do with my poor English ability. by the way, Didn't the way Dave showed
to you help you to solve your problem and still have had trouble with your
case?

keiji

I can't get what your validation rule is from your
formula(=Left(range("xcodes"),4)).
I might be wrong but Is that the leftmost 4 characters of the text must be
unique your rule or else?

keiji






- Show quoted text -

The users do not really know what D123 stands for, only a select group
of administrative staff really work with these codes. My range
includes perhaps four dozen unique Dcode entries (D123, D120, D713...)
and if I include the description, then the user knows the appropriate
selection. My challenge is that I only have room for the most useful
piece of data which is the 4 characters. What I describe I wish to do
is done quite often in databases where multiple fields are shown in a
dropdown list but only a value in a selected field is actually saved
as the value in the table.