locked
Multiple equation between two cells RRS feed

  • Question

  • Dears,

    I would like to make a formula to count how many dozens and items in a storehouse.

    1 Dozen = 12 Items.

    e.g. i have two cells D1 and E1. 

    I if enter in D1= 1 (dozen) it would write in E1= 12 (items). and if i enter in E1= 12 (items) it would write in D1= 1 (dozen).

    If i enter in E1= 24-48... etc (items) . it would write 1-2-3... etc in D1.

    If i enter less than 12 like 5 (items) in E1, it will write 0 in D1.

    If i enter a number not being multiplied by 12 in E1 e.g.= 43 it would write in D1 the lesser dozen which is 3 (3*12=36).

    And vice-versa for D1, if i write 4 dozens in D1 it would count that as 48(4*12) items per dozen in E1 cell.


    Please help me in writing this formula for the two columns of E and D.

    Appreciating your support,

    Monday, February 12, 2018 12:36 PM

Answers

  • Since you want this to work both ways, it cannot be done using formulas; it requires VBA code.

    Right-click the sheet tab, and select View Code from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim c As Range
        If Not Intersect(Range("D:D"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each c In Intersect(Range("D:D"), Target)
                If c.Value = "" Or Not IsNumeric(c.Value) Then
                    c.Offset(0, 1).ClearContents
                Else
                    c.Offset(0, 1).Value = 12 * c.Value
                End If
            Next c
            Application.EnableEvents = True
        End If
        If Not Intersect(Range("E:E"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each c In Intersect(Range("E:E"), Target)
                If c.Value = "" Or Not IsNumeric(c.Value) Then
                    c.Offset(0, -1).ClearContents
                Else
                    c.Offset(0, -1).Value = c.Value \ 12
                End If
            Next c
            Application.EnableEvents = True
        End If
    End Sub

    This will affect the entire columns D and E; you can adjust the ranges if needed.

    Switch back to Excel, and save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by olymyoted Monday, February 12, 2018 3:37 PM
    Monday, February 12, 2018 3:22 PM

All replies

  • Since you want this to work both ways, it cannot be done using formulas; it requires VBA code.

    Right-click the sheet tab, and select View Code from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim c As Range
        If Not Intersect(Range("D:D"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each c In Intersect(Range("D:D"), Target)
                If c.Value = "" Or Not IsNumeric(c.Value) Then
                    c.Offset(0, 1).ClearContents
                Else
                    c.Offset(0, 1).Value = 12 * c.Value
                End If
            Next c
            Application.EnableEvents = True
        End If
        If Not Intersect(Range("E:E"), Target) Is Nothing Then
            Application.EnableEvents = False
            For Each c In Intersect(Range("E:E"), Target)
                If c.Value = "" Or Not IsNumeric(c.Value) Then
                    c.Offset(0, -1).ClearContents
                Else
                    c.Offset(0, -1).Value = c.Value \ 12
                End If
            Next c
            Application.EnableEvents = True
        End If
    End Sub

    This will affect the entire columns D and E; you can adjust the ranges if needed.

    Switch back to Excel, and save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by olymyoted Monday, February 12, 2018 3:37 PM
    Monday, February 12, 2018 3:22 PM
  • Thank you dear for taking the time to write this code for me.

    Appreciating your support Mr.Hans.

    Monday, February 12, 2018 3:41 PM