Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
KimStarbase
 
Posts: n/a
Default 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
  #2   Report Post  
Anne Troy
 
Posts: n/a
Default 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



  #3   Report Post  
Debra Dalgleish
 
Posts: n/a
Default 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

  #4   Report Post  
Otto Moehrbach
 
Posts: n/a
Default 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



  #5   Report Post  
Otto Moehrbach
 
Posts: n/a
Default 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





  #6   Report Post  
KimStarbase
 
Posts: n/a
Default 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




  #7   Report Post  
Otto Moehrbach
 
Posts: n/a
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Printing data validation scenarios SJC Excel Worksheet Functions 14 July 24th 05 12:43 AM
Data Validation gbeard Excel Worksheet Functions 1 May 3rd 05 09:09 AM
data validation gbeard Excel Worksheet Functions 2 May 2nd 05 09:57 PM
Data validation not showing drop down Lily Excel Worksheet Functions 1 April 1st 05 07:13 PM
Effect of Conditional Formatting, Data Validation Bill Sturdevant Excel Discussion (Misc queries) 1 January 26th 05 12:50 AM


All times are GMT +1. The time now is 12:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"