Changing a cell value from another cell.

[Bouvougan]

Here's what I'm trying to do. I've created a drop-down list in a cell. I use
IF statements in two other cells that change the value of those cells
depending on the value selected in the drop-down list. This works fine for
the automatic value entry in the cells with the IF statements, but I also
need to allow the user to select a "Manual Entry" value from the drop-down
list that requires the user to manually enter values in the cells that have
the IF statements. Of course when they do this, the IF statements are
overwritten by the manually entered value. So if the user wants to select
another list option, the automatic value entry no longer works.

Is there some way to make the two cells with the IF statements allow manual
entry without overwriting the functionality of the IF statements? What I'm
hoping is that perhaps a third cell can be used to determine the value of the
cells that currently contain the IF statements. For example, perhaps this
third cell can contain an IF statement related to the drop-down list, then
conditionally supply the value to the other cell depending on the selection
in the drop-down list. That way entering values into that cell would not
overwrite a formula in that cell.

Any help would be greatly appreciated. I'm using Excel 2003, SP3.

 

[Bouvougan]

Your solution is close to what I need, but I'd still like to do a bit more.
It would be very good if the manual entry field (B1 in your example) would
revert to blank or a zero value if the automatic entry field is used. Your
example is similar to the way I have it set up now, but it allows values to
appear in the manual entry field even after a selection is made that enters
values in the automated field (C1). This could be really confusing for the
user.

Thanks for your reply.

 

[Bouvougan]

I'm afraid you're going beyond my limited knowledge of Excel. I have no idea
how to implement the scripting for this event.