drop down box

Posted on 2011-10-26
Last Modified: 2012-05-12
I have alot of cells on some of my sheets that I would like the user to be able to type common things in easily and to type new thing in as well. But rather than chosing the words can it be like predictive text. eg I would have a list of the most common works and if they type say S then the work Sick would come up. Common words are:


Or do you have any other suggestions? this has to happen about 10 - 20 times on a single sheet.

Question by:RobJanine
    LVL 6

    Accepted Solution

    If the common words are going to be in the same column, you can pre-fill several rows above the input range and hide these rows.  Excel has a built in predictive text option when you begin typing in a cell.

    So, as an example you can enter the pre-filled list (SICK,RDO,OFF,HOLIDAY) starting in A1.  Do this for each column you'd like to pre-fill.  Hide these rows from the user.

    Now when the user types the letter "S", all they have to do is press ENTER.

    They will have to enter more than one letter if there are words that begin the same letter(s).  For example, you would have to enter "SIC" if you had SICK and SINGLE in your pre-filled list.

    Also, as the user enters other unique values in the column, these will also become available in the predictive text.

    Author Comment

    Ok, only thing is I dont have any free cells above, can it be linked to a single cell for predictive text?
    LVL 6

    Expert Comment

    No, each item has to be in it's own cell.

    If you cannot insert rows above your input range, the only other way is to write a class that would watch inside of your input range and search the current column based on each keystroke and present a word or list of words.  Unfortunately, I'm not even really sure if this can be done.
    LVL 50

    Expert Comment

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now