Hi,
On an Excel worksheet I have a drop down list that consists of five selections. For each selection there is price association. The source data for the dropdown list is located on a separate worksheet. For three of the selections I have a predefined price and for the other two selections I would like to have the user be able to input the price as these vary based on different factors. There is a column for a quantity and in the next column I would like to have a formula calculate the total of the quantity x the price.
I have been able to use a nested IF function to get this to work partially. The real problem that I am running into is when I want the user to input their own value for the price. If they pick an item with a predefined price there is no problem because the price column populates from the data on the other worksheet. However, as soon the user inserts their own price into the price cell, it overwrites the formula. The other problem is that if the Price cell is blank it shows #VALUE! in the total calculation cell. I would like it to not show that error message if the price cell is blank.
The solution I am looking for would look like this - if the drop down list ="Item A" then leave the cell for the price blank and let the user input their own number. In the next cell calculate the total price of the cell based on the quantity selected. If the drop down list ="Item B" populate the price cell with the predefined price then do the price calculation.
I would also like to have the cells with the formulas repopulate the formulas automatically if someone tries to delete them.
I know the solution to this lies in a VBA script but I have no idea how to do VBA programming.
Thanks. <!-- / message --><!-- attachments -->
On an Excel worksheet I have a drop down list that consists of five selections. For each selection there is price association. The source data for the dropdown list is located on a separate worksheet. For three of the selections I have a predefined price and for the other two selections I would like to have the user be able to input the price as these vary based on different factors. There is a column for a quantity and in the next column I would like to have a formula calculate the total of the quantity x the price.
I have been able to use a nested IF function to get this to work partially. The real problem that I am running into is when I want the user to input their own value for the price. If they pick an item with a predefined price there is no problem because the price column populates from the data on the other worksheet. However, as soon the user inserts their own price into the price cell, it overwrites the formula. The other problem is that if the Price cell is blank it shows #VALUE! in the total calculation cell. I would like it to not show that error message if the price cell is blank.
The solution I am looking for would look like this - if the drop down list ="Item A" then leave the cell for the price blank and let the user input their own number. In the next cell calculate the total price of the cell based on the quantity selected. If the drop down list ="Item B" populate the price cell with the predefined price then do the price calculation.
I would also like to have the cells with the formulas repopulate the formulas automatically if someone tries to delete them.
I know the solution to this lies in a VBA script but I have no idea how to do VBA programming.
Thanks. <!-- / message --><!-- attachments -->
