if.... Anyway around this?

[Guest]

I'm new to this. I need to drop a value into a cell where there is nothing.
The 'target' cell should not have a formula because some days I would need
to place a one time value there , doing so would wipe out the formula.

Example:

A1 B1 C1
BOB JOE =IF(A1="bob",A2=B1)

A2
(BLANK)


I need the blank cell (A2) to take the value of B1 if C1 is true.
Maybe I have the syntax wrong, maybe it's not possible. Let me know.

Thanks

 

[Guest]

In C1, enter
=A1="bob"

In A2, enter
=IF(C1, B1, "")

or leave C1 empty and enter in A2
=IF(A1="bob", B1, "")

You cannot write a function in cell C1 that will place a value in A2.
Functions return values, they don't manipulate other objects (at least not
when called from a worksheet).

 

[britwiz]

Hi

This may get you started:

Right-click on the sheet tab and select View Code.

Paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A1") Then Exit Sub
If Range("A1").Value = "bob" Then
Range("A2").Value = Range("B1").Value
End If
End Sub

The code only runs if cell A1 has been changed and only checks to see
if "bob" has been entered in that cell, if it has then if copies the
data in B1 to A2.

Regards

Steve