Running Calculations Based on Selections from a Drop Down

Hi Everyone,

I am trying to create a macro that will do a simple calculation by subtracting the values from two different cells, each taken from a different sheet, and then add the value from a third cell to come up with a final answer. What's making this so challenging is that I want to incorporate a drop down list to determine the first value to be considered in the equation. There are (3) different values in a column on a separate sheet that correspond to the values in the drop down list. When I select one of the list items I want the macro to choose the corresponding value for the first value of the equation. For example, say my list allows me to select 8" slabs, 12" slabs or 12.5" slabs, these items correspond to 8, 12 and 12.5 on the other sheet. So, if I pick 8" slabs I want the macro to pick 8 as the first variable.

I eventually want to add some additional complexity by having the macro chose the second value which is subtracted from the value above. This second variable also comes from a list which will correspond to a cell value on a separate sheet. I think if I can get the first part to work I may be able to figure out this second part, but I'm uncertain how to incorporate the drop down list to begin with. I would appreciate any help offered on this.

Below is a simple macro I wrote to test the basic idea (I'm sure there's a more efficient way of doing this). If this code can be modified to achieve my goal how would I go about modifying it?

In summary, the basic equation is Y = A - B + C. A is chosen from a list of three options that correspond to a selection from the drop down list. B is chosen from another column on a third sheet which corresponds to a value found in a cell on the first sheet and then C is simply a value manually imputed on the first sheet. Hope this all makes sense. Again, I appreciate any help you can offer on this.

Please Login or Register  to view this content.

Greetings!
It'd be easy just to use a formula to finish the calculations.
A=

Formula:

Please Login or Register  to view this content.

Looks up input
From the table on Data1
Returns with that value

Nest it in the rest of your formula for Y
Y=A-B+C

Formula:

Please Login or Register  to view this content.

Example attached on DATA1

I was going to make the same suggestion as Kirkules, above. I'd personally use a combination of INDEX and MATCH; does the same thing but can be more flexible. This will give you figure "A"

Formula:

Please Login or Register  to view this content.

Figure "B" is simply Data2!A4 and "C" is Input!K1

This gives you the formula for F32 on the print sheet as

Formula:

Please Login or Register  to view this content.

Of course, you can adapt the INDEX/MATCH formula to fit other dropdowns or options in the calculation and substitute "B" and/or "C" accordingly

Tim