We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

drop down list data change in excell

Hassanay
Hassanay asked
on
Medium Priority
234 Views
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
loan.xlsx
Comment
Watch Question

byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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. http://contextures.com/xlDataVal02.html
byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
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(F$2="","",HLOOKUP(F$2,B$8:H$16,MATCH(E3,B$8:B$16,0),FALSE))
=IF(F$2="","",HLOOKUP(F$2,B$8:H$16,MATCH(E4,B$8:B$16,0),FALSE))
=IF(F$2="","",HLOOKUP(F$2,B$8:H$16,MATCH(E5,B$8:B$16,0),FALSE))
=IF(F2="","",IF(SUM(F3:F5)<=35%,"Approved","Not Approved"))

loanQ26971962.xlsx

Author

Commented:
TAHNSK FOR YOUR SOLUTION,BUT ACTUALLY I MISSED SOMETHING,
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

Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.