Can I put a dropdown on a cell to select a value to be used within a formula?

I'm working on making an Excel Macro for grabbing data from one sheet, putting it on another sheet to then be able to look at and do some math with. So far, I am able to grab the data and I have it on the new sheet. I have 2 columns that need to be calculated using that data via a formula. However, one of the values within that formula is different depending on the item (the value that changes is a percentage). What I want to be able to do is to have something like a drop down menu on each item where I can type in/select the two values/percentages that corresponds with that item, and then those two values will be used within the formula to calculate the corresponding values in the 2 columns. Here is a picture to better show what I mean:

cwExcel.PNG

For example, say my discount is 50% for Item A. The formula for My Price on Item A (Cell G2) would be =SUM(E2-(E2*50%)).

Also, say the customer discount on item A is 40%. The formula for Customer Price on Item A (Cell I2) would be =SUM(E2-(E2*40%)).

Say my discount on item B is 45% and the customer discount for item B is 30%. The formula for My Price on Item B (Cell G3) would be =SUM(E3-(E3*45%)). The formula for Customer Price on Item B (Cell I3) would be =SUM(E3-(E3*40%)). So on an so forth for each item...

The same formula is used for both columns, but the values can be different depending on the item. Each item will have a different percentages for both me and the customer.

Basically, I would like to have a dropdown either on all the cells in Columns G and I OR on each item in column A where I'm able to type in or select the two percentages to be used within that formula, and then after selecting/typing those, the corresponding values in columns G and I will be calculated for that item.

In my VBA code of my macro, I would ultimately like to just place the dropdowns on the needed cells, and then I can manually go through each item and set the values. That way right after my macro finishes, I am able to then go through my items and select the two percentages to be used, which will then be used in the formula to populate the values for My Price and Customer Price.

I first just want to know if something like this is possible in general, whether that be in VBA code, or just manually doing this in Excel. Any help is appreciated!

Add data validation to your cells, with the list option, and the list:

0,5,10,15,20,25,30,35,40,45,50

Then:

1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed

Please Login or Register  to view this content.