+ Reply to Thread
Results 1 to 10 of 10

How to create a dropdown that displays number for the selection

  1. #1
    Registered User
    Join Date
    04-13-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    How to create a dropdown that displays number for the selection

    Hi all
    Is it possible to create a dropdown that has the selections a, b, c. However, I want it display 1 for a, 2 for b, 3 for c once it's selected in the same cell as the dropdown box.

    Thanks!!
    excelnub

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: How to create a dropdown that displays number for the selection

    Look at this video. About 1/2 way into it Mike discusses combo boxes. It is close to what you are looking to do, but I think you will find it helpful.

    http://www.datapigtechnologies.com/f...tcontrols.html
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-13-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: How to create a dropdown that displays number for the selection

    Hi
    Thanks for quick response. I was looking into how I can display the numerical value in the same cell as the dropdown box rather than in another cell.
    THanks

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to create a dropdown that displays number for the selection

    I'm intrigued - why would you want to display a 1 if the User has chosen "a" ? Won't the User be a bit confused, and then try to select "a" again ?

    Pete

  5. #5
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: How to create a dropdown that displays number for the selection

    Hello alex mu,

    Please try the attached sample Workbook.

    Regards
    Attached Files Attached Files
    Please consider:

    Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .

  6. #6
    Registered User
    Join Date
    04-13-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: How to create a dropdown that displays number for the selection

    Thanks Winon this is exactly what I needed. Can you show me how it's done?

  7. #7
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: How to create a dropdown that displays number for the selection

    Hi alex mu,

    Your requirement goes begging for some VBA manipulation, which is exactly what I have done.

    Open my sample Workbook, and on the sheet where all the "Magic" appears, right click on the Sheet tab, and then select "View Code".

    You should now be in the VBA Project, where you can see that if your selection in the Dropdown = "a", it should return the value in the column adjacent to the one which contains "a".

    Actually, quite simple.

    Any further questions, please ask.

    If you are satisfied with the solution I had given you, then please mark your Thread as Solved.

    You may also Click on the Star to the far left, at the bottom of this Post, to Add Reputation.

    Regards

  8. #8
    Registered User
    Join Date
    04-13-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: How to create a dropdown that displays number for the selection

    Thanks again!
    Just a couple of learning points:
    1. If on the same worksheet I want to make another dropdown and another macro (in C column) to change the value to a different set of corresponding number, the range in the following line would be what i'm changing correct?:

    [If Not Intersect(Target, Range("B3:B10")) Is Nothing Then] ---> [If Not Intersect(Target, Range("C3:C10")) Is Nothing Then]

    2. What does this condition do? [If Target.Cells.Count > 1 Then Exit Sub]

    3. Lastly, if I change the Range value in any of the cells from F3-F9. Is there a way to refresh the macro to apply the changes to the dropdown selection cell?
    To clarify if I want a to become 100 now instead of 1. I change cell F3 = 100. How to i make that show up in B3.

    Alex

  9. #9
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: How to create a dropdown that displays number for the selection

    Hi alex mu,

    Oh my, you do ask a lot of questions which cannot be answered in one shot.

    Let us take it step by step.

    1. Yes, provided you have the matching alternative in the adjacent column.

    2. Actually you can change this:

    Please Login or Register  to view this content.
    to,

    Please Login or Register  to view this content.
    2. What does this condition do? [If Target.Cells.Count > 1 Then Exit Sub]
    With the Worksheet_Change_Event, if you should have selected more than one Cell or Column/s for any editing, the Code has to do nothing, and leave you be.

    3. Whatever you want the "a", "b" or "c" show as, just change the required data in Column F.


    Wow, hope that helps!

    Regards

  10. #10
    Forum Guru Winon's Avatar
    Join Date
    02-20-2007
    Location
    East Rand, R.S.A.
    MS-Off Ver
    2010
    Posts
    6,113

    Re: How to create a dropdown that displays number for the selection

    Hello alex mu,

    Thank you for adding to my Reputation.

    Since it seems to appear that we have managed to sort out your issue, please mark this thread as Solved.

    Click on the "Thread Tools" icon just above your post screen, and select "Sovled".

    Thank you,

    Regards.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create a list of companies based that match dropdown selection
    By aduquet in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-29-2013, 02:46 PM
  2. [SOLVED] create hyperlink to another sheet when specific selection is made on a dropdown
    By FlyFisherman in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2013, 11:32 AM
  3. Replies: 2
    Last Post: 10-07-2013, 11:06 AM
  4. Cull dropdown list options based on selection in another dropdown
    By Kiffar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2012, 01:53 AM
  5. Replies: 2
    Last Post: 08-11-2011, 01:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1