Making drop-down options more user friendly while retaining formula functionality

[ajf93]

Hello,

I am making a fairly complex sheet, but struggling with one part of a calculating form I need to add in, I suspect it's quite simple.

I have created a form that utilises 3 different variables to give me a total. Two of these are simple numerical entries, the third is a drop-down option.

My issue is that I want the drop-down option to be user friendly for the audience it is going to be used by. The rule I need is to add a different percentage for different year options in the drop-down (e.g. selecting 2 years will add 4%).

As an example, if I enter 10 in the first cell (cell C5), £50 in the second (cell C8), and the drop-down option in the third (cell C11), I should get the result £20. This gives me the formula =SUM((C5*C8)*C11).

If I write the numbers (in the above example it would be 0.04) as the selection for the drop-down box, the formula/form work fine, but this isn't clear enough for the people who will be using it. I can't make the option '2 days' or I get the error result #VALUE!. Is there a way to link the drop-down option to a formula but have it displayed as 'x years' in the drop-down?

I have attached a test copy, in case anyone wants to play with it and can offer any advice. Thanks a lot!

[Glenn Kennedy]

With a minor modification to the DV and data layout:

=SUM((C5*C8)*VLOOKUP(C11,$F$18:$G$20,2,FALSE))

BtW, try to avoid using merged cells ANYWHERE. If you want a ow bigger, adjust its height or width.

[ajf93]

Perfect - thank you very much! :D

[Glenn Kennedy]

You're welcome and thanks for the rep.