Biff is correct - this kind of thing could be handled with some code attached
to the worksheet itself. Essentially the code would trigger on the
Worksheet's Change event and determine: if a change had been made to a cell
adjacent to a cell containing a "Y", and if that is true then if that change
wasn't to the value in B1, then force it back to being the contents of B1.
The Application.Intersect method would be the one I'd use to start testing
the conditions.

"kvnexcel" wrote:

Thanks, Biff. Appreciated.

"Biff" wrote:

Hi!

A formula can't "push" a value to another cell. It can only "pull" a value
and return that value to the cell that contains the formula.

Some clever person (that eliminates me!) can probably write some VBA code to
do this for you.

Biff

"kvnexcel" wrote in message
...
Can a formula print a result in a target cell, one that doesn't have a
formula itself because it also may be used for text entry?
I want users to be able to type text into cells in the B column. The
exception is, when there is a Y in an A-column cell, I want B1's content
(in
this case, "RED") to be copied into target cell B3. The Y is the
controlling
factor -- if it's there, B3 says RED; if the Y is not there, the user can
enter own text.
Is this dual-nature B3 possible? It seems like the answer may be an
IF in another cell that prints a result into B3. Like in the movie
`Broadcast
News,' I `say it here and it comes out there.'
A B
1 RED
2
3 Y