Using a cell as input to select a pull down value, but same cell needs to be updated when pull down is manually chosen

[rayman1974]

Hi there,

For my work I am in the process of creating an excel to track a hiring process and the excel needs some automation. I have defined a dropdownlist with 6 items (phases) that applies to cell I3. Cell I3 contains a formula that looks to a number of cells and depending on whether a date is filled out in one or more of those cells one of the phases in the dropdownlist is automatically selected. One specific cell, let's say Cell Q3, already gets a value from the start based on some other cell to make sure that the corresponding phase in the pulldown is skipped. This works.

Now here's the thing. It needs to be possible to override the formula in I3 and to manually select that particular phase from the pulldown (it actually is a phase called "MT approval" which is normally not needed, only when this manual override is done). Manual selection is possible and the formula will disappear. However, I need that cell Q3 to be blank in this case, because in this situation a date needs to be filled out in cell Q3. When I put a formula in Q3 to do this I get a circular reference because the original formula in I3 will look at Q3 which contains a formula regarding I3.

So the question is: how do I get this Q3 cell empty when I have manually selected the corresponding option in I3 (and thereby removing the original formula in I3)

And I am looking for a smart trick bypassing macro or VBA

Thanks so much for your help!

Rayman