When creating a dropdown list, how can I have an item appear for choice but
output from choice an abbreviation? Example, I have a department list in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the description.
Any way to do this?

There is a sample file that demonstrates this:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
When creating a dropdown list, how can I have an item appear for choice
but
output from choice an abbreviation? Example, I have a department list in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the description.
Any way to do this?

Thank you thank you T.!

"T. Valko" wrote:

There is a sample file that demonstrates this:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
When creating a dropdown list, how can I have an item appear for choice
but
output from choice an abbreviation? Example, I have a department list in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the description.
Any way to do this?

This sample show the return values as numeric and uses 'If Target.Cells.Count
1'
How can I adapt to return my abbreviations? My sheet with the list is
'ChoiceLists', my named range is 'DEPT'. My sheet where choice is made is
'Projects'. Where is the code that would point to the abbreviation that goes
with the choice? So far the user is seeing the full choices and it is
returning the same full choice, not the abbreviations which is in the column
in front of the full description. I added the code
Here is my code adapted, but of course there is no count going on.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("ChoiceLists").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("ChoiceLists").Range("DEPT"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub


"T. Valko" wrote:

There is a sample file that demonstrates this:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
When creating a dropdown list, how can I have an item appear for choice
but
output from choice an abbreviation? Example, I have a department list in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the description.
Any way to do this?

My sheet where choice is made is 'Projects'.

Tell me *exactly* where your drop down cell is. Is there just a single drop
down?

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
This sample show the return values as numeric and uses 'If
Target.Cells.Count
1'
How can I adapt to return my abbreviations? My sheet with the list is
'ChoiceLists', my named range is 'DEPT'. My sheet where choice is made is
'Projects'. Where is the code that would point to the abbreviation that
goes
with the choice? So far the user is seeing the full choices and it is
returning the same full choice, not the abbreviations which is in the
column
in front of the full description. I added the code
Here is my code adapted, but of course there is no count going on.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("ChoiceLists").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("ChoiceLists").Range("DEPT"), 0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub


"T. Valko" wrote:

There is a sample file that demonstrates this:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data
Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
When creating a dropdown list, how can I have an item appear for choice
but
output from choice an abbreviation? Example, I have a department list
in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the
output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the
description.
Any way to do this?

Sheet 'Projects', starting cell C2 on down
Example cell C1 says 'DEPT'
Cell C2,C3,C4 etc have the dropdown. User click on cell C2, sees and clicks
on dropdown, the descriptive list shows (Accounting, Purchasing, etc). The
user clicks a choice from the dropdown list (say, Accounting), right now the
cell C2 get filled with 'Accounting'. I want the abbreviation to come back
instead. ie user sees and clicks on 'Accounting' but 'ACTG' is returned.
My actual list - namedrange 'DEPT' is on sheet 'ChoiceLists'. Cell C1 says
DEPT, Cell D3 says 'Department'
The abbreviations are in cells C3-C10 (or so), the descriptions are in cells
D3-D10.


"T. Valko" wrote:

My sheet where choice is made is 'Projects'.

Tell me *exactly* where your drop down cell is. Is there just a single drop
down?

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
This sample show the return values as numeric and uses 'If
Target.Cells.Count
1'
How can I adapt to return my abbreviations? My sheet with the list is
'ChoiceLists', my named range is 'DEPT'. My sheet where choice is made is
'Projects'. Where is the code that would point to the abbreviation that
goes
with the choice? So far the user is seeing the full choices and it is
returning the same full choice, not the abbreviations which is in the
column
in front of the full description. I added the code
Here is my code adapted, but of course there is no count going on.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("ChoiceLists").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("ChoiceLists").Range("DEPT"), 0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub


"T. Valko" wrote:

There is a sample file that demonstrates this:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data
Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
When creating a dropdown list, how can I have an item appear for choice
but
output from choice an abbreviation? Example, I have a department list
in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the
output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the
description.
Any way to do this?

Ok, your description is a little confusing!

Let's assume the drop down lists are in the range Projects!C2:C5

The departments are in the named range Depts on sheet ChoiceLists!D3:D5
The department codes (abbreviations) are in the range ChoiceLists!C3:C5

Navigate to sheet Projects
Right click on the sheet tab and select View code
Copy/paste the code below into the window that opens

Modify this line of the code to reflect your actual range size where the
drop down lists are located:

If Not Intersect(Target, Me.Range("C2:C5")) Is Nothing Then

If the actual range is C2:C25, then change to:

If Not Intersect(Target, Me.Range("C2:C25")) Is Nothing Then

'Start of code-----------

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Not Intersect(Target, Me.Range("C2:C5")) Is Nothing Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("ChoiceLists").Range("D3") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("ChoiceLists").Range _
("Dept"), 0) - 1, -1)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub



--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
Sheet 'Projects', starting cell C2 on down
Example cell C1 says 'DEPT'
Cell C2,C3,C4 etc have the dropdown. User click on cell C2, sees and
clicks
on dropdown, the descriptive list shows (Accounting, Purchasing, etc). The
user clicks a choice from the dropdown list (say, Accounting), right now
the
cell C2 get filled with 'Accounting'. I want the abbreviation to come
back
instead. ie user sees and clicks on 'Accounting' but 'ACTG' is returned.
My actual list - namedrange 'DEPT' is on sheet 'ChoiceLists'. Cell C1
says
DEPT, Cell D3 says 'Department'
The abbreviations are in cells C3-C10 (or so), the descriptions are in
cells
D3-D10.


"T. Valko" wrote:

My sheet where choice is made is 'Projects'.

Tell me *exactly* where your drop down cell is. Is there just a single
drop
down?

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
This sample show the return values as numeric and uses 'If
Target.Cells.Count
1'
How can I adapt to return my abbreviations? My sheet with the list is
'ChoiceLists', my named range is 'DEPT'. My sheet where choice is made
is
'Projects'. Where is the code that would point to the abbreviation
that
goes
with the choice? So far the user is seeing the full choices and it is
returning the same full choice, not the abbreviations which is in the
column
in front of the full description. I added the code
Here is my code adapted, but of course there is no count going on.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then GoTo exitHandler

If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("ChoiceLists").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("ChoiceLists").Range("DEPT"),
0),
0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

End Sub


"T. Valko" wrote:

There is a sample file that demonstrates this:

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data
Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP


"amaries" wrote in message
...
When creating a dropdown list, how can I have an item appear for
choice
but
output from choice an abbreviation? Example, I have a department
list
in
namedrange DEPT.
ACTG (Accounting)
PURC (Purchasing)
INVT (Inventory)

I want the user to see Accounting, Purchasing, Inventory, but the
output
from their choice to be the appreviated version (ACTG, PURC, INVT).
I don't want to display the entire thing to the user only the
description.
Any way to do this?