# Excel 2007: How to change formulas/multipliers for a cell based on selections made on a drop down list?

I Need help with an excel formula. I made a list with the data validation option. Three options in the drop down list. does not except blank. For each option, I want to change the formula in another cell.

The list is in b23. based on which of the three options are selected I want d23 to multiply the sum of (d18-d22) by .02 or .01 or by .39 and display it. What formula do I write in d23?

This seems like a pretty basic question. Any basic guides on the topic would also be greatly appreciated.

-

Here's two possible options: an easy way that doesn't look as nice, and a harder way that's a bit friendlier to users.

### Use the multiplier values as the entries in the Validation list

Put your three multiplier values somewhere in your spreadsheet, perhaps `G17:G20`.

``````| VALUES |
| 0.02   |
| 0.01   |
| 0.39   |
``````

In cell `B23`, set the data validation Source to be `=\$G\$18:\$G\$20`. Finally, in cell `D23`, your formula should be: `=SUM(D18:D22) * B23`.

### Use VLOOKUP() and a table to display labels, but select values

Here, you need a separate table that has the multipliers, and names you want displayed for each one. Here's some example values for cells `F17:G20`

``````| LABEL | VALUE |
| Tax A | 0.02  |
| Tax B | 0.01  |
| Tax C | 0.39  |
``````

In cell `B23`, set the data validation Source to be `=\$F\$18:\$F\$20`. Then, set the formula in `D23` to this:

``````=SUM(D18:D22) * VLOOKUP(B23, F18:G20, 2)
``````

As you can see, the second option really isn't that much harder, and can make it easier for users (and/or you). Your spreadsheet is a littler easier to understand, too, since the values are clearly labeled. They can even live on a separate worksheet, along with some documentation about what they're for.

-