Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
Data Validation | Excel Worksheet Functions | |||
data validation | Excel Worksheet Functions | |||
Data validation not showing drop down | Excel Worksheet Functions | |||
Effect of Conditional Formatting, Data Validation | Excel Discussion (Misc queries) |