Forum Discussion
pd1006
Jun 28, 2023Copper Contributor
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?
- NikolinoDEGold Contributor
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.