Data entry and calculations in the same cell

5 pts.
Tags:
Data conversion
excel
Excel Cell Formatting
Excel formulas
I have an excel template and would like to allow the user to input their date in one of two ranges and have the other range calculated automatically based on a formula to use in each cell This means that the formula needs to be outside the cells in the two ranges as they are both used for date entry For example Enter pounds The first range A1:M1 Then data would be converted to Kilos in Range A2:M2 Or , Enter Kilos in range A2:M2 Then data would be converted to Pounds in Range A1:M1 thank you so much

Software/Hardware used:
excel

Answer Wiki

Thanks. We'll let you know when a new response is added.

Some VBA code like this should work:

<pre>Private Sub Worksheet_Change(ByVal Target As Range)
Dim newValue As Double
Dim newCell As Range
If Target.Row < 3 Then
If Target.Row = 1 Then
Set newCell = Cells(2, Target.Column)
newValue = Target.Value / 2.205
ElseIf Target.Row = 2 Then
Set newCell = Cells(1, Target.Column)
newValue = Target.Value * 2.205
End If
If newCell.Value <> newValue Then
newCell.Value = newValue
End If
End If
End Sub</pre>

-CarlosDL

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following