Convert Data based on value selected in drop down menu

**cassandra23** · 08-26-2009, 02:04 PM

MY SETUP

My excel page includes a page of data with values in each cell calculated based on a simple formula:

$2.40 $4.80
$4.80 $9.60
$7.20 $14.40 and so on....

For example, $2.40 was calculated by multiplying $.10 (cell i7) * 24(cell i8) *1 (cell c12)

PROBLEM I"M TRYING TO SOLVE:

I want to create a drop down menu ranging from 1% to 100%, so that when I select a value between 1 and 100%, all the data on that page automatically converts to the selected %.

For example, if I select 10% in the drop down menu, $2.40 will automatically convert to $0.24; and $4.80 will convert to $0.48 cents and so on.....

Can anyone help ?

Thanks

**Palmetto** · 08-26-2009, 03:03 PM

See attached. Is this what you want?

**ChemistB** · 08-26-2009, 03:11 PM

First, a dropdown menu from 1-100 is quite a long dropdown list. You sure you don't just want user input?

Can you just include the cell which has your dropdown into your equations?

The following VBA code will multiple values in A1:A20 by the value in D1 whenever you change it but it will erase your formulas in A1:A20 also.

Please Login or Register  to view this content.

Any of that help?

**cassandra23** · 08-26-2009, 03:16 PM

Almost.

Can the "result" column convert automatically after choosing a Percentage ?

For example, if I select "20%" in the drop down menu, cell F8 would convert immediately to $0.48. along with all the other "amount" column values converting as well.

Originally Posted by Palmetto

See attached. Is this what you want?

**Palmetto** · 08-26-2009, 03:21 PM

Can the "result" column convert automatically after choosing a Percentage ?

For example, if I select "20%" in the drop down menu, cell F8 would convert immediately to $0.48. along with all the other "amount" column values converting as well.

I don't follow you. Choosing the percentage in the drop down already does the conversion. F8 holds the conversion of D8 multiplied by the chosen percentage.

Please clarify what you mean.

I think ChemistB is right about the long drop down list. It would be better to simply use A1 as an input cell, but use Data Validation to limit the maximum number to 100.

**cassandra23** · 08-26-2009, 03:34 PM

That makes sense. I'll do exactly that. Thanks for the input ChemistB and Palmetto.

Originally Posted by Palmetto

I don't follow you. Choosing the percentage in the drop down already does the conversion. F8 holds the conversion of D8 multiplied by the chosen percentage.

Please clarify what you mean.

I think ChemistB is right about the long drop down list. It would be better to simply use A1 as an input cell, but use Data Validation to limit the maximum number to 100.