Can a cell be *either text or the result of a formula?

[Guest]

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

 

[Biff]

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

 

[Guest]

The answer is pretty much No - a cell either contains a formula or it
contains something other than a formula. If a cell starts out containing a
formula and someone just types stuff into that cell, the formula is deleted
and whatever they typed stays behind.

Now, if you are talking about people typing into column A when there's not a
Y in a cell, we can do this kind of thing with an IF formula in column B.

Maybe an example will help you. Given the layout you've shown, put this
formula in B3:
=IF(A3="Y",$B$1,A3)
That says "if cell A3 contains a Y, then display the contents of cell B1
(always B1 because the $ symbols lock the reference) but if A3 doesn't
contain "Y" then echo whatever is in A3 here in B3.

But if someone types into B3, then the formula is going to go bye bye.

 

[Guest]

Thanks, Biff. Appreciated.

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

 

[Guest]

Thank you, appreciated

The answer is pretty much No - a cell either contains a formula or it
contains something other than a formula. If a cell starts out containing a
formula and someone just types stuff into that cell, the formula is deleted
and whatever they typed stays behind.

Now, if you are talking about people typing into column A when there's not a
Y in a cell, we can do this kind of thing with an IF formula in column B.

Maybe an example will help you. Given the layout you've shown, put this
formula in B3:
=IF(A3="Y",$B$1,A3)
That says "if cell A3 contains a Y, then display the contents of cell B1
(always B1 because the $ symbols lock the reference) but if A3 doesn't
contain "Y" then echo whatever is in A3 here in B3.

But if someone types into B3, then the formula is going to go bye bye.

 

[Guest]

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.

 

[Biff]

I posted this last night but for some reason it didn't make it through to
get posted. My OE says it was sent and it shows that I did reply but the
post never got posted! So, I'll try it again!

See the reply by J.E. Mcgimpsey:

http://tinyurl.com/r9a78

Biff