Forum Discussion
How to create drop down list but show different values in Excel?
Difficult one - I need to create a drop-down box that when I select the dropdown value, it returns an abbreviated word. I have a worksheet that has over 100 drop-down boxes but all the info is the same. I have attached a file and highlighted the information on the 2nd page. thank you very much in advance.
Obviously, you already know how to do data validation as your schedule is full of it. What exactly do you want to return and where? You speak of "an abbreviated word" and "highlighted information in the 2nd page". You mean the yellow cells in columns L:M? The words are exactly the same as in column A (=basis for all your drop-downs). What's the purpose of the sequential number (1-29) in col M?
Perhaps you intend to write abbreviations in L1:L29 and have these displayed in columns B:G on "Sheet1", depending on the selections made. For example, select "Competitive Catalyst" from the drop-down and then return "CompCat". That could be tricky. So, please clarify your intentions.
8 Replies
Obviously, you already know how to do data validation as your schedule is full of it. What exactly do you want to return and where? You speak of "an abbreviated word" and "highlighted information in the 2nd page". You mean the yellow cells in columns L:M? The words are exactly the same as in column A (=basis for all your drop-downs). What's the purpose of the sequential number (1-29) in col M?
Perhaps you intend to write abbreviations in L1:L29 and have these displayed in columns B:G on "Sheet1", depending on the selections made. For example, select "Competitive Catalyst" from the drop-down and then return "CompCat". That could be tricky. So, please clarify your intentions.
do you think that it is something you can help with? Riny_van_Eekelen
My recommendation would be to re-work the Instrument names to a shorter version to begin with and use these in the dropdowns. Then you can have a list with the full Instrument names on the side, if needed, for reference. Not sure how you could have a dropdown with the full name and have it replaced by the abbreviation in the same cell. It would violate the whole idea of having data validation in the first place. A work-around could be to introduce helper columns that look up the abbreviation, based on the name in the dropdown. I've done that in the attached example. I made up some abbreviations in the 2nd page and entered some formulae in the yellow cells in Sheet1. Press the minus-sign above columns C and E to hide columns B and D (you can read more about "Data / Group and outline" in the help screens, in case you are not familiar with it). Hopefully, either of these solutions may work for you. If not, someone else might come up with a better idea.
the only reason I copied and pasted on 2 page to the right is so that i didnt have to add an addional column and mess up the formulas I already have in place. Riny_van_Eekelen
ok, this is exactly what I am looking for, For example, select "Competitive Catalyst" from the drop-down and then return "CompCat". I am waiting on the abbreviated words for all of the list in column "L" from my wife and her company. the words as they are, are too long to fit in the cells and when I expand the columns from 6 to 10, it will be really busy on there. they do have abbreviations, I am just waiting on them. I did copy the items on the 2nd page to the yellow part just to show what I am needing ( I will plug in the correct abbreviations once I get it to work correctly. I did stumble across this formula to plug in, and spent hours on it trying to get it to work. I could get it to work on a whole new blank sheet, but I have 0 experience in that. when I did get it to work on a whole new sheet, well then I could not drag it to new cells for copy and paste. thank you for your help.. jason
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20161026
selectedNa = Target.Value
If Target.Column = 5 Then
selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdowna"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub Riny_van_Eekelen
- Hello,
Kindly see how to create drop-down list in the link below
https://support.office.com/en-us/article/create-a-drop-down-list-7693307a-59ef-400a-b769-c5402dce407bthank you, but i am tring to return a different value from the items in the drop down box. Abiola1
