Hi,
I have a scenario where I have a dropdown box in column K (using data validation) giving options of different types of unit that are available to hire, in column M there is a list of hire periods (1-2 days, a week, a month etc). I have a formula in column N that looks at these cells and depending which options are chosen, performs a vlookup on different price tables and populates the cell in N with the hire cost.
However we have one final option on the drop down in K, for misc costs. Sometimes there are delivery charges etc which are priced ad-hoc.
I would like a piece of code that will clear the contents of cell N, if "misc" is chosen in K (column M becomes irrelevant here), so that the user can just input the price.
My formula in N uses IF statements so if the Misc option is chosen the cell still appears blank and we can just type over the top, but I would ideally like to have it physically clear the contents. I know some of the users of the sheet are likely to double click into the cell out of habit when pasting (to make sure none of the formatting is copied as we are pasting the prices from a website) and that clicks them into the formula and confuses things as it's a long formula.
I have looked around but I can only find options to clear a cell whenever the dropdown is changed, but I don't want to do that - just when this specific option is chosen. I can't quite get my head around how to adapt these pieces of code to do what I want.
I have a scenario where I have a dropdown box in column K (using data validation) giving options of different types of unit that are available to hire, in column M there is a list of hire periods (1-2 days, a week, a month etc). I have a formula in column N that looks at these cells and depending which options are chosen, performs a vlookup on different price tables and populates the cell in N with the hire cost.
However we have one final option on the drop down in K, for misc costs. Sometimes there are delivery charges etc which are priced ad-hoc.
I would like a piece of code that will clear the contents of cell N, if "misc" is chosen in K (column M becomes irrelevant here), so that the user can just input the price.
My formula in N uses IF statements so if the Misc option is chosen the cell still appears blank and we can just type over the top, but I would ideally like to have it physically clear the contents. I know some of the users of the sheet are likely to double click into the cell out of habit when pasting (to make sure none of the formatting is copied as we are pasting the prices from a website) and that clicks them into the formula and confuses things as it's a long formula.
I have looked around but I can only find options to clear a cell whenever the dropdown is changed, but I don't want to do that - just when this specific option is chosen. I can't quite get my head around how to adapt these pieces of code to do what I want.
