Can you add formulas into drop boxes??

[Guest]

I'm wondering if excel has the capabilites to add different formulas to the
drop down boxes.
IE: option to choose between the sum of a number with different percentages
applied to the total...

 

[Guest]

Yes with a little help from VBA.

Say we have some value in A1 and we want to pick formulas from a dropdown to
go in B1.

Somewhere in the worksheet have a list of formulas:

'=A1*1.05
'=A1*0.1
'=A1*0.15

The single quotes (apostrophes) are important.

Set up data validation in B1 to point to this table. This will select one
of the three formulas (as text).

Finally in worksheet code, enter the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
' gsnuxx
If Not Intersect(Target, Range("B1")) Is Nothing Then
If Left(Target.Value, 1) = "=" Then
Application.EnableEvents = False
Target.Value = Target.Value
Application.EnableEvents = True
End If
End If
End Sub



The data validation pick the formua and the macro converts it from text into
a "real" formula.