• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

drop down list data change in excell

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
0
Hassanay
Asked:
Hassanay
  • 3
1 Solution
 
byundtCommented:
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
0
 
byundtCommented:
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
0
 
HassanayAuthor 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

0
 
byundtCommented:
=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.

Brad
loanQ26971962.xlsx
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now