4

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
9a3eedi
  • 696
  • 2
  • 7
  • 18

2 Answers2

5

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.

assylias
  • 321,522
  • 82
  • 660
  • 783
  • Very nice, assylias! You beat me to the punch with the VBA function :) – Joseph Apr 24 '12 at 17:27
  • Thanks! Is there any possible way to do this without using a VBA function? I've been meaning to avoid it if possible. – 9a3eedi Apr 24 '12 at 17:31
  • @9a3eedi I don't think there is a built-in function that does that. Maybe someone else does. Any specific reasons why you don't want to use VBA? – assylias Apr 24 '12 at 17:37
  • @assylias This question is for a friend. I don't think he knows anything about VBA. Guess I'll just have to teach him, shouldn't be too hard. – 9a3eedi Apr 24 '12 at 17:39
  • In this case it is fairly straigthforward: open the Macro editor, create a new module, copy paste the code and you are done. You can find plenty of tutorials on google if needed. See my edits for a solution without VBA. – assylias Apr 24 '12 at 17:46
  • Sounds simple enough. Thanks! – 9a3eedi Apr 24 '12 at 21:51
1

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

enter image description here

Joseph
  • 5,070
  • 1
  • 25
  • 26
  • Not sure why this got downvoted - it might be a sensible approach depending on the requirement of the OP. – assylias Apr 24 '12 at 17:49
  • I have no idea why I got downvoted either. I thought it was helpful. This is like the second time in a row. Makes me not want to post anything anymore :P – Joseph Apr 24 '12 at 17:52
  • 1
    Somebody might think that this is only a partial answer as it does not cover other functions but I don't think it deserves a downvote + it is good practice for downvoters to leave a comment so that answers can be deleted if really wrong or improved if they are partially wrong. Keep posting and don't forget that a downvote is -2 vs. an upvote +10 ;) – assylias Apr 24 '12 at 17:55
  • Thanks for the encouragement, assylias! I'm still learning the etiquette around here so your comments help me out a lot. I'll keep at it :) – Joseph Apr 24 '12 at 17:59