Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




DROP DOWN MENUS with formulas
I am trying to create an easy budget. I have a drop down menu with pay
periods or bill payments as daily, weekly, biweekly, monthly etc. These are all in a drop down menu, but now I need to link the chosen drop down menu period to calculate everything into a monthly payment amount. I have tried "vlookup" but without success, maybe I don't understand it enough, but is any way to link a drop down menu choice to a formula? Thanks 
#2
Posted to microsoft.public.excel.misc




DROP DOWN MENUS with formulas
See if this page of Debra Dalgleish helps:
http://www.contextures.com/xlOrderForm01.html  HTH, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "curiousgeorge" wrote in message ... I am trying to create an easy budget. I have a drop down menu with pay periods or bill payments as daily, weekly, biweekly, monthly etc. These are all in a drop down menu, but now I need to link the chosen drop down menu period to calculate everything into a monthly payment amount. I have tried "vlookup" but without success, maybe I don't understand it enough, but is any way to link a drop down menu choice to a formula? Thanks 
#3
Posted to microsoft.public.excel.misc




DROP DOWN MENUS with formulas
On Apr 12, 6:54 pm, curiousgeorge
wrote: I am trying to create an easy budget. I have a drop down menu with pay periods or bill payments as daily, weekly, biweekly, monthly etc. These are all in a drop down menu, but now I need to link the chosen drop down menu period to calculate everything into a monthly payment amount. I have tried "vlookup" but without success, maybe I don't understand it enough, but is any way to link a drop down menu choice to a formula? Thanks Let's say you have your dollar amount in A2, and your dropdown list of pay periods in B2, and you want A2*(the value of B2) to appear in C2... (For the sake of solving the problem at hand, we will assume for now that every month has 30 days, or 4 weeks.) You need to assign a "# of days" value to each pay period. For example, "daily" = 30 because if you pay something daily, you will pay it 30 times in a month. Thus, A2*30 would give you the correct result. The value "weekly" would be 4; "biweekly" would be 2; and "monthly" would be 1. So, the array that you are pulling your dropdown list data from, which contains "daily", "weekly", "biweekly", and "monthly" should be given a name. (Insert Name Define...) We'll call it "Period". The formula in C2 should be: =A2*(CHOOSE(INDEX(MATCH(B2,Period,0),1), 30,4,2,1)) MATCH finds the value of B2 in the list "Period"; INDEX turns that into a number (1 thru 4); and CHOOSE picks the appropriate number (30,4,2,1) based on that INDEX number. Hope that makes sense! 
#4
Posted to microsoft.public.excel.misc




DROP DOWN MENUS with formulas
Shonzi:
It seems to work with daily, but any other choice remains the same answer as daily. I included three more areas under A2 for family income, when I copy the cell nothing seems to work but daily. I also increased the numbers to reflect a yearly amount divided by 12, once again it only works with the daily choice. Any ideas? Thanks for your help. George "Shonzi" wrote: On Apr 12, 6:54 pm, curiousgeorge wrote: I am trying to create an easy budget. I have a drop down menu with pay periods or bill payments as daily, weekly, biweekly, monthly etc. These are all in a drop down menu, but now I need to link the chosen drop down menu period to calculate everything into a monthly payment amount. I have tried "vlookup" but without success, maybe I don't understand it enough, but is any way to link a drop down menu choice to a formula? Thanks Let's say you have your dollar amount in A2, and your dropdown list of pay periods in B2, and you want A2*(the value of B2) to appear in C2... (For the sake of solving the problem at hand, we will assume for now that every month has 30 days, or 4 weeks.) You need to assign a "# of days" value to each pay period. For example, "daily" = 30 because if you pay something daily, you will pay it 30 times in a month. Thus, A2*30 would give you the correct result. The value "weekly" would be 4; "biweekly" would be 2; and "monthly" would be 1. So, the array that you are pulling your dropdown list data from, which contains "daily", "weekly", "biweekly", and "monthly" should be given a name. (Insert Name Define...) We'll call it "Period". The formula in C2 should be: =A2*(CHOOSE(INDEX(MATCH(B2,Period,0),1), 30,4,2,1)) MATCH finds the value of B2 in the list "Period"; INDEX turns that into a number (1 thru 4); and CHOOSE picks the appropriate number (30,4,2,1) based on that INDEX number. Hope that makes sense! 
#5
Posted to microsoft.public.excel.misc




DROP DOWN MENUS with formulas
On Apr 13, 11:26 am, curiousgeorge
wrote: Shonzi: It seems to work with daily, but any other choice remains the same answer as daily. I included three more areas under A2 for family income, when I copy the cell nothing seems to work but daily. I also increased the numbers to reflect a yearly amount divided by 12, once again it only works with the daily choice. Any ideas? Thanks for your help. George "Shonzi" wrote: On Apr 12, 6:54 pm, curiousgeorge wrote: I am trying to create an easy budget. I have a drop down menu with pay periods or bill payments as daily, weekly, biweekly, monthly etc. These are all in a drop down menu, but now I need to link the chosen drop down menu period to calculate everything into a monthly payment amount. I have tried "vlookup" but without success, maybe I don't understand it enough, but is any way to link a drop down menu choice to a formula? Thanks Let's say you have your dollar amount in A2, and your dropdown list of pay periods in B2, and you want A2*(the value of B2) to appear in C2... (For the sake of solving the problem at hand, we will assume for now that every month has 30 days, or 4 weeks.) You need to assign a "# of days" value to each pay period. For example, "daily" = 30 because if you pay something daily, you will pay it 30 times in a month. Thus, A2*30 would give you the correct result. The value "weekly" would be 4; "biweekly" would be 2; and "monthly" would be 1. So, the array that you are pulling your dropdown list data from, which contains "daily", "weekly", "biweekly", and "monthly" should be given a name. (Insert Name Define...) We'll call it "Period". The formula in C2 should be: =A2*(CHOOSE(INDEX(MATCH(B2,Period,0),1), 30,4,2,1)) MATCH finds the value of B2 in the list "Period"; INDEX turns that into a number (1 thru 4); and CHOOSE picks the appropriate number (30,4,2,1) based on that INDEX number. Hope that makes sense! Hide quoted text   Show quoted text  It turns out my original formula had an extra, unnecessary bit to it. You don't need the INDEX function, but it will work with it in there. That being said... When I built a test Excel sheet to answer your question, it worked perfectly. Here's what I did: Column A: This is an input cell, where you would type the amount of money per payment Column B: This is the dropdown validation list, referencing the array that I named "Period" (this array was located in cells E1:E4) Column C: This cell contains the formula that calculates the monthly payment amount; it multiplies Column A by the number of payments, based on the selection in Column B. Let's look at the formula again, so that we can see precisely what is going on (that way you can modify it as needed): =A2*(CHOOSE(MATCH(B2,Period,0),30,4,2,1)) First, let's look at: MATCH(B2,Period,0) This finds an exact match for the current value in cell B2, looking in the array named "Period" (cells E1:E4 in this example). It returns a number from 1 to 4, based on which Row the value B2 is found in the array Position. Now, let's say MATCH returns the number 2 because you chose "Weekly" in your dropdown list (and "Weekly" was in cell E2). That leaves us with: =A2*(CHOOSE(2,30,4,2,1)) The way the CHOOSE function works is, the first number in the ( ) is the reference, followed by a list of possibilities. Those possibilities have been hardcoded to 30 (as in 30 days per month), 4 (as in 4 weeks per month), 2 (as in 2 biweekly periods per month), and 1 (as in one payment per month). CHOOSE takes the value of the first, second, third, or fourth item in the list of possibilities, based on the reference that is calculated by the MATCH function (1 thru 4). Hopefully, by understanding the logic, you can adapt this formula to your needs. Let me know if it works! 
#6
Posted to microsoft.public.excel.misc




DROP DOWN MENUS with formulas
Shonzi
Good Job! I changed the name of the range somehow and when I changed it back to what it should be, it worked like a charm. Thanks again! George "Shonzi" wrote: On Apr 13, 11:26 am, curiousgeorge wrote: Shonzi: It seems to work with daily, but any other choice remains the same answer as daily. I included three more areas under A2 for family income, when I copy the cell nothing seems to work but daily. I also increased the numbers to reflect a yearly amount divided by 12, once again it only works with the daily choice. Any ideas? Thanks for your help. George "Shonzi" wrote: On Apr 12, 6:54 pm, curiousgeorge wrote: I am trying to create an easy budget. I have a drop down menu with pay periods or bill payments as daily, weekly, biweekly, monthly etc. These are all in a drop down menu, but now I need to link the chosen drop down menu period to calculate everything into a monthly payment amount. I have tried "vlookup" but without success, maybe I don't understand it enough, but is any way to link a drop down menu choice to a formula? Thanks Let's say you have your dollar amount in A2, and your dropdown list of pay periods in B2, and you want A2*(the value of B2) to appear in C2... (For the sake of solving the problem at hand, we will assume for now that every month has 30 days, or 4 weeks.) You need to assign a "# of days" value to each pay period. For example, "daily" = 30 because if you pay something daily, you will pay it 30 times in a month. Thus, A2*30 would give you the correct result. The value "weekly" would be 4; "biweekly" would be 2; and "monthly" would be 1. So, the array that you are pulling your dropdown list data from, which contains "daily", "weekly", "biweekly", and "monthly" should be given a name. (Insert Name Define...) We'll call it "Period". The formula in C2 should be: =A2*(CHOOSE(INDEX(MATCH(B2,Period,0),1), 30,4,2,1)) MATCH finds the value of B2 in the list "Period"; INDEX turns that into a number (1 thru 4); and CHOOSE picks the appropriate number (30,4,2,1) based on that INDEX number. Hope that makes sense! Hide quoted text   Show quoted text  It turns out my original formula had an extra, unnecessary bit to it. You don't need the INDEX function, but it will work with it in there. That being said... When I built a test Excel sheet to answer your question, it worked perfectly. Here's what I did: Column A: This is an input cell, where you would type the amount of money per payment Column B: This is the dropdown validation list, referencing the array that I named "Period" (this array was located in cells E1:E4) Column C: This cell contains the formula that calculates the monthly payment amount; it multiplies Column A by the number of payments, based on the selection in Column B. Let's look at the formula again, so that we can see precisely what is going on (that way you can modify it as needed): =A2*(CHOOSE(MATCH(B2,Period,0),30,4,2,1)) First, let's look at: MATCH(B2,Period,0) This finds an exact match for the current value in cell B2, looking in the array named "Period" (cells E1:E4 in this example). It returns a number from 1 to 4, based on which Row the value B2 is found in the array Position. Now, let's say MATCH returns the number 2 because you chose "Weekly" in your dropdown list (and "Weekly" was in cell E2). That leaves us with: =A2*(CHOOSE(2,30,4,2,1)) The way the CHOOSE function works is, the first number in the ( ) is the reference, followed by a list of possibilities. Those possibilities have been hardcoded to 30 (as in 30 days per month), 4 (as in 4 weeks per month), 2 (as in 2 biweekly periods per month), and 1 (as in one payment per month). CHOOSE takes the value of the first, second, third, or fourth item in the list of possibilities, based on the reference that is calculated by the MATCH function (1 thru 4). Hopefully, by understanding the logic, you can adapt this formula to your needs. Let me know if it works! 
#7




Quote:
Still cant get this to work...selects weekly only =A2*(CHOOSE(INDEX(MATCH(B2,period,0),1),52,26,12,2 )) My formulae had the index function and without...but still only claculates weekly amount over a year no matter what drop down is selected....... 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Using Data from Drop Down Menus in Sum Formulas  Excel Worksheet Functions  
Drop down menus  Excel Worksheet Functions  
Dropdown Menus  Excel Discussion (Misc queries)  
formulas for drop down menus  Excel Discussion (Misc queries)  
Drop down menus  Excel Discussion (Misc queries) 