Drop-down menus

[Amy]

I am trying to set up a drop down menu where the user
selects a word from a list, then based on which item they
select, the formulas in the worksheet change.

For example,
Selection1 would equal 0
Selection2 would equal .1
Selection2 would equal .15
Selection4 would equal .06

I have the drop down box that shows Selection1,
Selection2, etc, but I am not sure how to tie the formulas
and the selection together.

 

[Steve McBride]

If your selection list is less than 7, and if you can add another column,
then you can use data validation to ensure the user selects one of your
options, and then in the extra column, use a simple formula like:

=IF(A1="Selection 1",0,IF(A1="Selection 2",0.1,IF(A1="Selection
3",0.15,IF(A1="Selection 4",0.06,""))))

In the example, Column A is the "data validated" fields and the formula can
reside in B column (or wherever you would like). You can then use the .1 or
..15 to do further calculations.

Hope this helps!
Steve

 

[Biff]

Hi Amy,

Are you using drop down lists created with data validation?
There are a few ways to do this. Depending on how many
variables you have in the list, you could use an IF
function in your formula. Say the drop down is in A1. The
formula could be something like:

=IF(A1=Section1,A100+0,IF(A1=Section2,A100+.1,A100))

or

You could create a lookup table and use the VLOOKUP
function:

=A100+VLOOKUP(A1,AA1:AB4,2,FALSE)

or

You could use defined names that refer to the values
Section1, Section2, etc.

Biff

 

[Biff]

oops! In the IF example SectionX should be enclosed in " ".

Biff