Evaluating a formula defined in another cell

excel xlm
9a3eedi picture 9a3eedi · Apr 24, 2012 · Viewed 10k times · Source

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

assylias picture assylias · Apr 24, 2012

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.

Related questions

Copy data from closed workbook based on variable user defined path

I have exhausted my search capabilities looking for a solution to this. Here is an outline of what I would like to do: User opens macro-enabled Excel file Immediate prompt displays for user to enter or select file path of …

Read More
Set the color of one cell based on the color of another cell

What I would like to have is: IF A1 in Sheet 2 is blue Then A1 in Sheet 1 changes to blue I know I can get the color of A1 in Sheet 2 by using: =GET.CELL(63,Sheet2!A1) (Excel: Can I …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More