calculating specific formulas based on selected species

Copper Contributor

I am trying to make a drug calculation sheet for our clinic. I put in dosing ranges based on weight so it calculates down the line when weight is entered. However, I would like to be able to create a "button" to select dog or cat and have that specify which lines are calculated. So some are calculated if I select dog and others if cat is selected. How can I do this?

1 Reply


Here is a VBA example that might help you.

Alternatively, you can use a combination of data validation, conditional formatting, and formulas.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then ' Change A1 to the cell address of the linked cell for the combo box
        Dim selectedSpecies As String
        selectedSpecies = Me.Range("A1").Value
        ' Clear previous calculations
        Me.Range("C2:C10").ClearContents ' Range for dog calculations
        Me.Range("C12:C20").ClearContents ' Range for cat calculations
        ' Calculate formulas based on selected species
        If selectedSpecies = "dog" Then
            ' Calculate dog formulas
            Me.Range("C2").Formula = "[dosage formula for dogs]" ' Replace with actual dog formula
            ' Repeat for other dog formulas if needed
        ElseIf selectedSpecies = "cat" Then
            ' Calculate cat formulas
            Me.Range("C12").Formula = "[dosage formula for cats]" ' Replace with actual cat formula
            ' Repeat for other cat formulas if needed
        End If
    End If
End Sub

 Hope it helps you.