Clearing a cell based on the value of another cell (INDIRECT data) without macros
 

Hi All. First thread so be gentle.

Excel 2007

I am using the INDIRECT function in Data Validation to drive the drop-down values available to users. A selection is made in K34 that drives the available options in M35.

The problem I have is that if a user goes back to K34 and changes their selection, the option they have chosen remains in M35. That option may now be invalid as the new selection in K34 has a different option set.

I need a way to blank M35 if a new selection is made in K34. I can't put a formula directly into M35 as it is user selectable and I can't use code or macros (customer security policy).

I'm currently using Conditional Formatting to change font to white when K34 is empty but of course they re-appear when K34 is changed (still with the wrong options in place).

Any ideas?