+ Reply to Thread
Results 1 to 5 of 5

adding functions to drop-down items

  1. #1
    Forum Contributor
    Join Date
    08-28-2005
    Posts
    132

    adding functions to drop-down items

    I have a drop down list in my speadsheet. This same drop down is repeated maybe 40 -50 times in the worksheet. I want to apply ablity of mulitiplication to my drop down items. so that based on the drop down choose a multiplier is added.

    b7....................c7......................d7.....................................e7
    monthly...........1500..................1500(or c7*1)...................4500( d7+prior d7 vals)
    weekly.............375....................1500(c7*4).......................3375
    bi-monthly........750....................1500(c7*2).......................3750
    quarterly..........4500..................1500 (c7/3).......................7500

    keep in mind that b7 is selected from the drop down, c7 is manually entered, d7 is autofilled from c7 input being * or / by b7 value, and e7 is the value of d7+that of previously saved d7 values.

    Please help me create formula or code that will allow me to add the required functions to the dropdown items to complete the functions required.

    Thank you.

  2. #2
    K Dales
    Guest

    RE: adding functions to drop-down items

    Is your dropdown a combobox created from the "Forms" toolbar? If so, this is
    what I would do. I assume you already have the combobox with the necessary
    values in it. I am not sure what all the options are so I will use the
    following: Quarterly, Monthly, Biweekly, Weekly. (by "Bi-Monthly" in your
    example it seems you mean twice monthly. Usually bi-something means every
    two, i.e. every two months; so to keep myself straight I am using biweekly
    here to mean the same thing (I think!))

    1) If not already there, I would create a column for the "Linked Cell" of
    the combobox. Hide the column if you don't want the user to see it. I will
    say this column was created as column F for your sheet.

    2) Now the formula for D would be this (will write it as for D7):
    =CHOOSE(F7,1/3,1,2,4)*C7
    The order of items in your combobox is important here: the formula looks at
    F7, which is a number corresponding to the choice selected, and then uses
    that to choose from the values listed here - so if the choice is Quarterly
    (choice #1 in the combobox) it uses 1/3; if it is Monthly (Choice #2) it uses
    1, etc...

    3) I have to make another assumption here: when you say "e7 is the value of
    d7+that of previously saved d7 values" you mean the sum for all the previous
    values in D where the combobox selection matches the current row (otherwise
    your totals don't add up).
    The formula for E would be this (illustrated as if in E7):
    =SUMIF($F$1:$F7,F7,D7)
    --
    - K Dales


    "oberon.black" wrote:

    >
    > I have a drop down list in my speadsheet. This same drop down is
    > repeated maybe 40 -50 times in the worksheet. I want to apply ablity
    > of mulitiplication to my drop down items. so that based on the drop
    > down choose a multiplier is added.
    >
    > b7....................c7......................d7.....................................e7
    > monthly...........1500..................1500(or
    > c7*1)...................4500( d7+prior d7 vals)
    > weekly.............375....................1500(c7*4).......................3375
    > bi-monthly........750....................1500(c7*2).......................3750
    > quarterly..........4500..................1500
    > (c7/3).......................7500
    >
    > keep in mind that b7 is selected from the drop down, c7 is manually
    > entered, d7 is autofilled from c7 input being * or / by b7 value, and
    > e7 is the value of d7+that of previously saved d7 values.
    >
    > Please help me create formula or code that will allow me to add the
    > required functions to the dropdown items to complete the functions
    > required.
    >
    > Thank you.
    >
    >
    > --
    > oberon.black
    > ------------------------------------------------------------------------
    > oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732
    > View this thread: http://www.excelforum.com/showthread...hreadid=494354
    >
    >


  3. #3
    Forum Contributor
    Join Date
    08-28-2005
    Posts
    132
    no this drop down was created using the Data > Validation option. Then I have a Name Range set to provide the info in the list.

    Should I use the combo box, if so how do I create the drop down using this method?

    Please advise?

  4. #4
    Forum Contributor
    Join Date
    08-28-2005
    Posts
    132
    a more detailed example of what I wish to do:

    I am creating a budget worksheet

    A1..................B1..................C1..................D1.................E1........................
    Item..............Frequency.......Amount..........Monthly Total.Yearly........
    income..........bi-weekly........$1000............$2000...........$24000...................

    Cell A1 will not change after being inserted, B1 will come from the drop down menu and remain the same, user will insert C1 on bi-weekly basis, while D1 will calculate user monthly salary based on the value entered into C1 either by multipling or dividing C1 by B1, and E1 will be calulated by 12 * the value of D1.

    So if the example above is for the first pay period and this is the second:

    A1..................B1..................C1..................D1.................E1........................
    Item..............Frequency.......Amount..........Monthly Total.Yearly........
    income..........bi-weekly........$750..............$1750...........$21000...................

    By updating the figures in C1, it as so updates D1 and E1 accordingly.

    However I need to come up with a way to record, what the actual monthly salary and yearly salary should be so that I can track it on a monthly basis to show if I am above or below my actual annual salary.

    I also want to show a monthly scope of pay so that if I look at the budget in March I can see my total pay in Jan. and in Feb.

    Thanks for the help I know this is a very heavy project for a novice but it is something I feel I must do. With a couple of kids, bad credit, high rent, and one kid heading into college. I need to know what going on with my money. And I cannot afford to buy any software. But will always invest in my own education if needed.

    Thanks.

  5. #5
    Forum Contributor
    Join Date
    08-28-2005
    Posts
    132
    still needing some help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1