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