Enter input in a cell based on selected option in a drop list in another cell (Excel 2003)

Posted on 2011-04-30
Last Modified: 2012-05-11
Hi everybody,

As a translator, I have worksheets on which I manage my translation projects. In those worksheets, one cell is dedicated to my client's name. Another cell is for the initials of the project manager for the current project. Each client has more than one project manager. Currently I have to manually select the client's name from a drop list (which values are from a range of cells in another worksheet) and the PM's name from another drop list (which values are also from another range of cells in another worksheet).

I would like that the second list (PMs' one) would be dynamically populated depending on the client's name selected in the first drop list.

How can I achieve this?

Many thanks in advance for any helpful answer.


Question by:pg111
    LVL 81

    Accepted Solution

    You can do what you want. Below are some techniques.

    To create a drop-down menu for a cell where the source list will vary in length select the cell and then choose the menu command Data->Validation to open the Data Validation dialog box. Choose List from the Allow drop-down list. In the Source text box enter the following formula:


    where A1 is the first cell in the list of values and A1:A100 is the array of cells representing the largest possible list. If the range of drop-down list values has been named then use this formula:


    Note that SUMPRODUCT is used instead of COUNTA because COUNTA counts cells with formulas even though the formula result is empty.

    To use a list that includes only values associated with a specific key:


    The example assumes there are two lists, one with keys (column A) and a second with list values (column B), and that each key is contiguous. The formula assumes a key is entered into C1.

    To use a list on another worksheet the INDIRECT function must be used since Excel does not allow direct references to other worksheets in a validation list formula:

       =OFFSET(INDIRECT("'Other Sheet'!A1"),0,0,COUNTA(INDIRECT("'Other Sheet'!A:A")),1)

    To use a list on another worksheet that includes only values with a specific key:

       =OFFSET(INDIRECT("'Other Sheet'!B1"),MATCH(A1,INDIRECT("'Other Sheet'!A:A"),0)-1,0,COUNTIF(INDIRECT("'Other Sheet'!A:A"),A1),1)

    To use a list on another worksheet that includes values in a column identified by a specific header identified by the value in A1:

       =OFFSET(INDIRECT("'Other Sheet'!A2"),0,MATCH(A1,INDIRECT("'Other Sheet'!A1:C1"),0)-1,COUNTA(OFFSET(INDIRECT("'Other Sheet'!A:A"),0,MATCH(A1,INDIRECT("'Other Sheet'!A1:C1"),0)-1))-1,1)

    The example assumes there are two columns on the source worksheet, one with keys (column A) and a second with list values (column B), and that each key is contiguous. The formula assumes a key is entered in cell A1.

    To use a list on another worksheet where the worksheet is entered in cell A1:

       =OFFSET(INDIRECT("'" & A1 & "'!A1"),0,0,COUNTA(INDIRECT("'" & A1 & "'!A:A")),1)

    To use the above formulas with the ListFillRange of a combo box or a list box, a named formula must be used. Choose the menu command Insert->Name->Define. In the top text entry field enter a name for the formula such as "MenuList". In the bottom text entry box enter one of the above formulas. Use absolute cell addressing ($A$1) to ensure that the cell references are not adjusted by Excel. Click Add and then click Done. Set the ListFillRange property to the value used to name the named formula. This technique works with both ActiveX and Forms controls.


    Author Comment

    Thanks Kevin. You're quick on the draft!
    I will look into your answer more carefully later and certainly revert to you with questions or just congrats!
    Thanks again.

    Author Closing Comment

    The matter is complex. Moreover I had to "translate" US Excel formulas into French ones. But the explanations were both accurate and comprehensive. Thanks and congrats to Kevin!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    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.

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

    779 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

    10 Experts available now in Live!

    Get 1:1 Help Now