Forum Discussion

pd1006's avatar
pd1006
Copper Contributor
Jun 28, 2023

calculating specific formulas based on selected species

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?

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    pd1006 

    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.

Resources