CODEWITHSUNDEEP
excelexcel-formulavba
RTFS
RTFS

Reputation: 115

excel 2013 update one cell from another and allow input in both

I'm using Excel 2013 and want to make a simple weight conversion sheet!

This is what I would like to achieve, i would like three cells C1, C2 & C3

C1 a user can enter Kg this then fills out C2 with the number of stones and C3 with the number of Lbs.

BUT I also want them to be able to enter in C2 and C3 the number of stones and Lbs and that to auto populate C1 with the amount in Kg.

Lastly they should also be able to enter the weight in Lbs eg 28lbs in C3 and this is then converted to Kg in C1 and stone and Lbs in C2 & C3.

The maths I think I can do, but how can I use a cell both so display a result and take an input?

Upvotes: 0

Views: 76

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

Place the following procedure into the worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.Column = 3 Then
            If Target.Row < 4 Then
                Application.EnableEvents = False
                Select Case Target.Row
                    Case 1
                        Cells(2, 3) = Target * 0.157473 '<-- kg to stone
                        Cells(3, 3) = Target * 2.20462  '<-- kg to pounds
                    Case 2
                        Cells(1, 3) = Target * 6.35029  '<-- stone to kg
                        Cells(3, 3) = Target * 14       '<-- stone to pounds
                    Case 3
                        Cells(1, 3) = Target * 0.453592 '<-- pounds to kg
                        Cells(2, 3) = Target * 1 / 14   '<-- pounds to stone
                End Select
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Upvotes: 2

Related Questions