Evaluating a formula defined in another cell

Question

Say I've got a function name in cell A1, like SUM, and some data in B1 and C1. Is there any way to define a formula in one cell such that it calls the formula that is defined in A1 and have it work on B1 and C1 as data?

So something like:
=A1(B1:C1) should be equal to =SUM(B1:C1) since A1 contains the word SUM in it.

Essentially, something like preprocessor macros in C, or function pointers maybe.

Answer 1

You could do it using vba by creating a user defined function in a module:

Public Function applyFunction(functionName As Range, argument As Range) As Variant
    applyFunction = Evaluate(functionName & "(" & argument.Address & ")")
End Function

If you put SUM in A1, and 1, 2, 3 in B1, B2, B3, =applyFunction(A1,B1:B3) will return 6. It is equivalent to calling =SUM(B1:B3).

EDIT

If you really don't want to use VBA, you can create a name (insert name in excel 2003 I think, Define Name in Excel 2010):

• Define a new name (let's say eval1)
• in the refers to area, enter =EVALUATE(A1&"(B1:B3)"), where A1 contains SUM and B1:B3 is the range with the numbers
• in a blank cell, type =eval1 and it should return the result

But this approach is less flexible.

Answer 2

If you want to use a formula instead, you could possibly use the SUBTOTAL() function. However, it is a little limited.

Check out the image. It uses the reference to the function number for subtotal. You can expand this by creating a vlookup function if you want to use the name of the function, but you also have to provide a way to determine to use the regular function num or the 101-type values which ignores hidden values in the data range.

Check out this link for more info: http://office.microsoft.com/en-us/excel-help/subtotal-function-HP010062463.aspx