drop down list data change in excell

Posted on 2011-04-22
Last Modified: 2012-05-11
i have an excell sheet ,the user will first choose the type from a drop down list which contains (Payroll,Staff,selfemployeed,other) , for each of these types we wull have three percents (percent 1, percent 2, percent 3).so if the user choose Payroll he will get (5%,6%,7%) for percent 1 , percent 2, percent 3)
if he choses Staff, he will get (7,8,10 %) for ( percent 1 , percent 2, percent 3)

and there is a list a field that must give him if the loan is approved or not, so if the summation of (percent 1 & percent 2 & percent 3)<35 then it must returns approved..else the filed must return not approved
Question by:Hassanay
    LVL 80

    Expert Comment

    The general procedure is to use named ranges for your second set (the dependent ones) of dropdown data. You reference these using the INDIRECT function and the choice from the first dropdown.
    LVL 80

    Expert Comment

    After looking at your sample file, I see that you aren't using dependent dropdowns at all. Instead, you need to do lookups using formulas for cells F3:F6 like:
    =IF(F2="","",IF(SUM(F3:F5)<=35%,"Approved","Not Approved"))


    Author Comment

    in (=IF(F2="","",IF(SUM(F3:F5)<=35%,"Approved","Not Approved")))
    ACTAULLAY if i chossed payrol the sum must be 20%
    if staff there is different perecntage 35%
    if self employeed 40%

    so i want the above formula daynamic & dependent on the user input in the list.
    i dont know if i shall sumbmit anew question as your answer was perfect as per my 1st request
    please advise

    LVL 80

    Accepted Solution

    =IF(F2="","",IF(SUM(F3:F5)<=LOOKUP(F2,{"Other","Payroll","Self employeed","Staff"},{0.4,0.2,0.4,0.35}),"Approved","Not Approved"))
    Note that LOOKUP is expecting the second parameter to be listed in alphabetical order. Note also that I replicated your typo in "Self employeed". I guessed what the cutoff percentage was for category Other.


    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

    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…
    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 demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    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

    11 Experts available now in Live!

    Get 1:1 Help Now