Working with Values from Combo Box / Drop Downs

I am doing a budget planner where the user selects from a dropdown list the frequency of outgoing.  So say a gas needs to be paid every 2 months the user will select this option and the in the cell next to it, key in the amount.  How can I gather all this data and get say a weekly amount required based on all weekly, fortnight, monthly, yearly values

2 thoughts on “Working with Values from Combo Box / Drop Downs

  1. John Douch

    OK. Try this.

    Type the following into the cells shown below…

    A2 Gas
    A3 Electricity
    A4 Mortgage
    A5 Savings

    A8 Weekly
    A9 Fortnightly
    A10 Monthly
    A11 Quarterly

    B8 1
    B9 2
    B10 13
    B11 52

    Now, go cell B2 and choose Data (or Data Tools) from the menu, then Data Validation.
    Choose List under Allow and =$A$8:$A$12 in the Source Box.

    Copy and paste (or drag) B2 to B3, B4 and B5. You should now have dropdowns in those four B column fields.

    In column C put the amount paid for each period.

    OK, nearly there…!

    In E2 type =$C2/VLOOKUP($B2,$A$8:$B$12,2,FALSE)*VLOOKUP($E$1,$A$8:$B$12,2,FALSE) and then copy that formula to E3, E4 and E5.
    In E6 type =SUM(E2:E5)

    Finally go to E1 and choose Data (or Data Tools) from the menu, then Data Validation.
    Choose List under Allow and put =$A$8:$A$12 in the Source Box.

    With a bit of luck that should be it. Depending on what you choose from the dropdown in E1 you will see the individual components for that period and the total will show up in cell E6.

    Cheers
    John

Leave a Reply

Your email address will not be published. Required fields are marked *