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.

 

[jamescox]

We have encountered similar situations where we need cell formulas to
bring live data into a workbook, but need a way to manually override the
live data if there are problems with it.

The relative inelegant solution we came up with was to have the
'automatic' value in cell C1, a place for manual entry in cell B1 and a
formula in cell A1 that was:

=IF(B1="",C1,B1)

or the perhaps more descriptive

=IF(ISBLANK(B1),C1,B1)

Any calculations involving the values of C1 or B1 instead reference/use
the value of A1.

Again, not elegant, but for our purposes, the ability for the user to
see what the automatic value is was helpful - and we wrote out to a data
historian the manually entered override if it was not blank, so having
it in a cell was handy.

 

[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.

 

[jamescox]

You may be able to get there by using the Worksheet_Change event. This
would allow you to detect changes in the cell with data validation and,
when appropriate, setting the value of B1 to ""

Note, however, that your logic on setting this up has to be based on
the user manually changing the cell with data validation - a cell whose
value changes as a result of a recalculation will not trigger the
Worksheet_Change event.

 

[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.

 

[jamescox]

If you've never done any VBA programming and have no other foreseeable
needs for that skill, you are probably going to need more help
integrating this potential solution into your workbook than it's
reasonable to try to give via this forum.

If you have done some other VBA coding and just aren't familiar with
the Worksheet_Change even, I can give you some jump-start code.

As an alternate to either of those, let's fall back and think about
what you are really trying to do. It sounds like you want a data
validation cell that will show your user some good choices, but will
also allow your user to input values that aren't in the data validation
list.

If that's accurate, a ActiveX (not a Forms Control) Combobox can give
you that capability. It can accept a range of cells as the list of
items (and a heading to display from a cell directly above that range
of cells) and the selection from the list OR the user-entered text (or
numeric value) can be linked to another cell. If you can implement your
data entry with that sort of fuctionality, then that's probably your
best course of action.