Can a Formula in Cell X modify Cell Y?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Example:

A Formula in Cell A1 should do the following:

If cell A1 equals 1, then cell B1 should have the value 5, otherwise B1
should be 6.

A condition in cell A1 is changing the value of cell B1... is that possible?

Please note that there is NO Formular in Cell B1, which is the cell to be
modified...

Thanks
 
Yes, you can just use a simple even macro like

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 1 Then Range("B1").Value = 5 Else Range("B1").Value =
6
End Sub


right click on the sheet tab in question and paste in the above, press Alt +
Q to close the VB editor
and test it
 
IT WORKED !!!

THANKS!!!!!!!!!!!!!!!!!!!!!!!!

Peo Sjoblom said:
Yes, you can just use a simple even macro like

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 1 Then Range("B1").Value = 5 Else Range("B1").Value =
6
End Sub


right click on the sheet tab in question and paste in the above, press Alt +
Q to close the VB editor
and test it
--

Regards,

Peo Sjoblom
Click to expand...
 
If you want cell B1 to change when A1 changes, you put the formula in B1. The
formula in A1 cannot directly change B1. It can only return a result to A1. To
accomplish what you want without a formula in B1 would require VBA
programming, which seems to me (without more information about WHY you can't
put a formula in B1) to be silly in this case.
 
Yes, it will work, BUT it slows down your worksheet because this sub runs
whenever you change ANY cell on the worksheet.

Please explain WHY you don't want a formula in B1. If you do that, that
formula will recalculate ONLY when A1 changes. It won't recalculate whenever
some other cell on the worksheet changes, as the macro does.
 
Back
Top