Here is my "best and final" offer.
The caveats are:
- The formula must be in the first (only) table on the spreadsheet.
- The column containing the drop down list (column E in the example) can be named anything, but if named other than formula, modify the call to the function accordingly: =MakeFormula([@formula])
- The formula must be in the column to the immediate right of the drop down list (column F in the example)
- The answers will appear in the column to the immediate right of the formula (column G in the example)
Again the restrictions keep from hard coding anything. Table name can be anything. You can have any number of columns and they can be named whatever you like. The drop down list can appear in any column as long as the two columns to the right are reserved as noted above.
You can come up with any formula Excel allows. This application works at the Excel and not the VBA level as the previous version did.
One more caveat. The sheet module for the sheet that contains the data table must have the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TableName As String
TableName = ActiveSheet.ListObjects(1).Name
If Intersect(Target, Range(TableName & "[formula]")) Is Nothing Then Exit Sub
ActivateFormula Target
End Sub
This code is the magic that converts the string returned by MakeFormula into a "real" formula.
Bookmarks