A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

DROP DOWN MENUS with formulas



 
 
Thread Tools Display Modes
  #1  
Old April 12th 07, 11:54 PM posted to microsoft.public.excel.misc
curiousgeorge
external usenet poster
 
Posts: 6
Default 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, bi-weekly, 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

Ads
  #2  
Old April 13th 07, 12:14 AM posted to microsoft.public.excel.misc
RagDyeR
external usenet poster
 
Posts: 3,572
Default 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, bi-weekly, 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  
Old April 13th 07, 01:01 AM posted to microsoft.public.excel.misc
Shonzi
external usenet poster
 
Posts: 10
Default 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, bi-weekly, 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; "bi-weekly" would be 2; and "monthly"
would be 1.

So, the array that you are pulling your dropdown list data from, which
contains "daily", "weekly", "bi-weekly", 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  
Old April 13th 07, 04:26 PM posted to microsoft.public.excel.misc
curiousgeorge
external usenet poster
 
Posts: 6
Default 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, bi-weekly, 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; "bi-weekly" would be 2; and "monthly"
> would be 1.
>
> So, the array that you are pulling your dropdown list data from, which
> contains "daily", "weekly", "bi-weekly", 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  
Old April 13th 07, 09:45 PM posted to microsoft.public.excel.misc
Shonzi
external usenet poster
 
Posts: 10
Default 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, bi-weekly, 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; "bi-weekly" would be 2; and "monthly"
> > would be 1.

>
> > So, the array that you are pulling your dropdown list data from, which
> > contains "daily", "weekly", "bi-weekly", 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 drop-down 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 hard-coded to 30 (as in 30 days per month), 4
(as in 4 weeks per month), 2 (as in 2 bi-weekly 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  
Old April 15th 07, 03:16 AM posted to microsoft.public.excel.misc
curiousgeorge
external usenet poster
 
Posts: 6
Default 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, bi-weekly, 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; "bi-weekly" would be 2; and "monthly"
> > > would be 1.

> >
> > > So, the array that you are pulling your dropdown list data from, which
> > > contains "daily", "weekly", "bi-weekly", 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 drop-down 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 hard-coded to 30 (as in 30 days per month), 4
> (as in 4 weeks per month), 2 (as in 2 bi-weekly 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  
Old December 10th 16, 09:19 AM
Dave1972 Dave1972 is offline
Junior Member
 
First recorded activity by ExcelBanter: Dec 2016
Posts: 3
Default

Quote:
Originally Posted by curiousgeorge View Post
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, bi-weekly, 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; "bi-weekly" would be 2; and "monthly"
> > > would be 1.

> >
> > > So, the array that you are pulling your dropdown list data from, which
> > > contains "daily", "weekly", "bi-weekly", 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 drop-down 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 hard-coded to 30 (as in 30 days per month), 4
> (as in 4 weeks per month), 2 (as in 2 bi-weekly 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!
>
>

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.......
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Data from Drop Down Menus in Sum Formulas Will Emms Excel Worksheet Functions 3 April 2nd 07 02:26 PM
Drop down menus Dannigirl426 Excel Worksheet Functions 3 January 12th 07 06:55 PM
Drop-down Menus master_lain Excel Discussion (Misc queries) 1 June 2nd 06 03:33 AM
formulas for drop down menus Char Excel Discussion (Misc queries) 0 July 18th 05 07:29 PM
Drop down menus Please help Excel Discussion (Misc queries) 1 January 29th 05 07:05 PM


All times are GMT +1. The time now is 12:12 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.