formula in one cell or another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tjb - 27 Mar 2006 00:07 GMT

When the user enters a number in cell A1, I want cell B1 to divide that
number by 8. If A1 is blank then B1 should be blank.

Alternately, when the user enters a number in cell B1, I want cell A1 to
MULTIPLY that number by 8. If B1 is blank then A1 should be blank.

I'm pretty sure this has to be a workbook function but I'm not sure how to
proceed. Any ideas all?

Reply to this Message

Elkar - 26 Mar 2006 23:55 GMT

What you're asking can't be done with worksheet functions. A cell may
contain user input OR a calculated formula. Not both. However, you can
accomplish what you want with VB Code.

Open the VB Editor (Alt-F11) and add this code to the "ThisWorkbook" object.

Dim D, M As Double

Private Sub Workbook_Open()

D = Range("A1").Value
M = Range("B1").Value

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Range("A1").Value <> D Then
D = Range("A1").Value
Range("B1").Value = Range("A1").Value / 8
Else:
If Range("B1").Value <> M Then
M = Range("B1").Value
Range("A1").Value = Range("B1").Value * 8
End If
End If

End Sub

Hope that helps,
Elkar

> When the user enters a number in cell A1, I want cell B1 to divide that
> number by 8. If A1 is blank then B1 should be blank.
[quoted text clipped - 4 lines]
> I'm pretty sure this has to be a workbook function but I'm not sure how to
> proceed. Any ideas all?

Reply to this Message

paul - 27 Mar 2006 00:05 GMT

in some other cell,=if(a1="","",a1/8,if(b1="","",b1*8))

Signature

paul
remove nospam for email addy!

Reply to this Message