Excel if statement for dropdown list

Posted on 2014-08-29
Last Modified: 2014-08-30
dear experts,

i have a dropdown list with different email suffixes like in cell B15

in cell B20 i have also a dropdown with changes different parameters depends on the values i can choose there GUL,GAL,TEST,GIL,DAL. Now i like to include a if statement in the suffixlist above depends on the value in C20
which should automatically change to the correct email suffix in B15.

Thanks in advance
Question by:Mandy_
    LVL 30

    Accepted Solution

    Once you put a formula in B15, then the drop down becomes "unnecessary" because it is your formula and not the dropdown which will determine the value. Correct?

    That said, you can just put this formular in B15
    =IF(ISBLANK(C20), "", LOWER(C20) & ".com")

    Open in new window

    LVL 2

    Author Comment


    thanks for your comment. I've 6 different values in C20 it stands for different companies. Every company getting
    his own email domains as suffix. If i change the value in this dropdown the smtp will automatically build
    for the selected value / company.

    Generally every value i choose has a fixed smtp. These 6 fixed prefixes (,....) in the dropdown should
    change automatically with the selected value in C20. Unfortunately some times there are exceptions.
    These exceptions (10) are also values of the dropdown in B15 but should select manually.
    I dont like to have 16 different values in C20 because the 10 values not needed very often but if one
    of them needed i will select manually.

    Thanks in advance

    i could use your formula instead "isblank" insert the selected value of c20?
    LVL 30

    Assisted Solution

    Dealing with those exceptions is the tricky bit. An alternative is to perhaps use three celles instead of two.

    C20: use as is
    B14: if user has selected one of the exceptions, then they must select/type a value here. You can use conditional formatting to set the textcolor in this cell to be the same as background colour of the cell if the user is not required to type in anything in there AND highlight the cell if they should do somethung in there (just a visual cue)
    B15: the final cell with a formula. If C20 is one of the ordinary scenarios, then just use formula above which just adds ".com" to C20. If it is one of the exceptions, then B14 will be highlighted and user has to select value or type in.

    I hope that makes sense. Its not possible to have one cell in which the user can either type in without overriding the formula
    LVL 2

    Author Closing Comment

    Thanks for your help

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    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 …

    759 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