Data Validation - 2 items for the same drop box
 

What I am trying to achieve is a drop down list that would look something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop down
box is in (a29) I would like to select one of those options and only have
D1a, or F1f, ect show up in the cell. The words are just references to my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic editor
and I don't have clue on how to use it.
Thanks for all your help

Data Validation - 2 items for the same drop box
 

I think you are talking about a conditional dropdown? Try this:
http://www.contextures.com/xlDataVal02.html
************
Anne Troy
www.OfficeArticles.com

"KimStarbase" wrote in message
...
What I am trying to achieve is a drop down list that would look something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop down
box is in (a29) I would like to select one of those options and only have
D1a, or F1f, ect show up in the cell. The words are just references to my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic
editor
and I don't have clue on how to use it.
Thanks for all your help

Data Validation - 2 items for the same drop box
 

You could do this with programming. There's a sample workbook on my web
site that shows a product name and code in the Data Validation dropdown
list. After an item is selected, the cell shows only the product name.
You may be able to adapt this to your workbook.

On the following page:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for "Data Validation Columns"


KimStarbase wrote:
What I am trying to achieve is a drop down list that would look something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop down
box is in (a29) I would like to select one of those options and only have
D1a, or F1f, ect show up in the cell. The words are just references to my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic editor
and I don't have clue on how to use it.
Thanks for all your help


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Data Validation - 2 items for the same drop box
 

Kim
That would take VBA. The following short macro will do that. When you
are setting up the Data Validation, click on the "Error Alert" tab and
uncheck "Show error alert after invalid data is entered."
This macro is triggered to execute if a change occurs in cell A29. If that
occurs, the macro will replace the entry in A29 with the first 3 characters
of that entry.
This macro is a sheet event macro and must be placed in the sheet module of
the sheet that contains the A29 cell Data Validation. To do that, first
select that sheet. Then right-click on the sheet tab of that sheet. Select
View code. Paste this macro into that module.
Take note that when a Data Validation cell is setup as described above, any
manual entry is allowed. And when any such entry is made, the macro will
fire and change the entry to the first 3 characters of the entry.
Additional code can be written into this macro to preclude the macro firing
if the entry is not in the desired list of allowable entries.
If you send me an email with a valid email address for you, I'll send you a
small file that has all this properly placed and operating. My email
address is . Remove the "nop" from this address. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A29" Then _
Target = Left(Target, 3)
End Sub
"KimStarbase" wrote in message
...
What I am trying to achieve is a drop down list that would look something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop down
box is in (a29) I would like to select one of those options and only have
D1a, or F1f, ect show up in the cell. The words are just references to my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic
editor
and I don't have clue on how to use it.
Thanks for all your help

Data Validation - 2 items for the same drop box
 

Kim
That would take VBA. The following short macro will do that. When you
are setting up the Data Validation, click on the "Error Alert" tab and
uncheck "Show error alert after invalid data is entered."
This macro is triggered to execute if a change occurs in cell A29. If that
occurs, the macro will replace the entry in A29 with the first 3 characters
of that entry.
This macro is a sheet event macro and must be placed in the sheet module of
the sheet that contains the A29 cell Data Validation. To do that, first
select that sheet. Then right-click on the sheet tab of that sheet. Select
View code. Paste this macro into that module.
Take note that when a Data Validation cell is setup as described above, any
manual entry is allowed. And when any such entry is made, the macro will
fire and change the entry to the first 3 characters of the entry.
Additional code can be written into this macro to preclude the macro firing
if the entry is not in the desired list of allowable entries.
If you send me an email with a valid email address for you, I'll send you a
small file that has all this properly placed and operating. My email
address is . Remove the "nop" from this address. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A29" Then _
Target = Left(Target, 3)
End Sub

"KimStarbase" wrote in message
...
What I am trying to achieve is a drop down list that would look something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop down
box is in (a29) I would like to select one of those options and only have
D1a, or F1f, ect show up in the cell. The words are just references to my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic
editor
and I don't have clue on how to use it.
Thanks for all your help

Data Validation - 2 items for the same drop box
 

Thanks for all of your help everyone.

"Otto Moehrbach" wrote:

Kim
That would take VBA. The following short macro will do that. When you
are setting up the Data Validation, click on the "Error Alert" tab and
uncheck "Show error alert after invalid data is entered."
This macro is triggered to execute if a change occurs in cell A29. If that
occurs, the macro will replace the entry in A29 with the first 3 characters
of that entry.
This macro is a sheet event macro and must be placed in the sheet module of
the sheet that contains the A29 cell Data Validation. To do that, first
select that sheet. Then right-click on the sheet tab of that sheet. Select
View code. Paste this macro into that module.
Take note that when a Data Validation cell is setup as described above, any
manual entry is allowed. And when any such entry is made, the macro will
fire and change the entry to the first 3 characters of the entry.
Additional code can be written into this macro to preclude the macro firing
if the entry is not in the desired list of allowable entries.
If you send me an email with a valid email address for you, I'll send you a
small file that has all this properly placed and operating. My email
address is . Remove the "nop" from this address. Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A29" Then _
Target = Left(Target, 3)
End Sub

"KimStarbase" wrote in message
...
What I am trying to achieve is a drop down list that would look something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop down
box is in (a29) I would like to select one of those options and only have
D1a, or F1f, ect show up in the cell. The words are just references to my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic
editor
and I don't have clue on how to use it.
Thanks for all your help

Data Validation - 2 items for the same drop box
 

Kim
I left out something in the macro I sent you. It should be as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A29" Then _
Application.EnableEvents = False
Target = Left(Target, 3)
Application.EnableEvents = True
End Sub

HTH Otto
"KimStarbase" wrote in message
...
Thanks for all of your help everyone.

"Otto Moehrbach" wrote:

Kim
That would take VBA. The following short macro will do that. When
you
are setting up the Data Validation, click on the "Error Alert" tab and
uncheck "Show error alert after invalid data is entered."
This macro is triggered to execute if a change occurs in cell A29. If
that
occurs, the macro will replace the entry in A29 with the first 3
characters
of that entry.
This macro is a sheet event macro and must be placed in the sheet module
of
the sheet that contains the A29 cell Data Validation. To do that, first
select that sheet. Then right-click on the sheet tab of that sheet.
Select
View code. Paste this macro into that module.
Take note that when a Data Validation cell is setup as described above,
any
manual entry is allowed. And when any such entry is made, the macro will
fire and change the entry to the first 3 characters of the entry.
Additional code can be written into this macro to preclude the macro
firing
if the entry is not in the desired list of allowable entries.
If you send me an email with a valid email address for you, I'll send you
a
small file that has all this properly placed and operating. My email
address is . Remove the "nop" from this address.
Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A29" Then _
Target = Left(Target, 3)
End Sub

"KimStarbase" wrote in message
...
What I am trying to achieve is a drop down list that would look
something
like this:

D1a - suipplies
F1g - cell phones
C2a - travel
E4b - computers

and so on and so forth. I can do that.
What I would like to happen though is in the same cell that the drop
down
box is in (a29) I would like to select one of those options and only
have
D1a, or F1f, ect show up in the cell. The words are just references to
my
budget, and I don't nned them after I selected the right one. Is this
possible?
I am somewhat new to excel and have never went into the visual basic
editor
and I don't have clue on how to use it.
Thanks for all your help