Evaluate a formula stored in a cell

iworknumbers

Is it possible to store an expression in a cell and then refer to it/evaluate it within other cells?

I've found myself repeatedly using complex expressions in cell formulas, e.g., the sub-expression OFFSET ( $A$2, 0, 0, ROW() -1, 1 ) to refer to a cell in a row and all rows above it is nested within many other expressions. As the expressions in cells get lengthier and more complex, it becomes harder to correctly write and debug them. I'd like to put subexpressions in their own cells and then evaluate them in the main cells, to make things easier to read (like macros in C).

Is this possible?

Best Answer

You can get a sort of macro facility working. Try this:

Insert a separate sheet, make the table name unique to the spreadsheet (I use "Macros"), and ensure the table has one header row and one header column. Then label the column A header "Name" and column B header "macro". This sets you up with a shorthand, self-documenting naming system. To add a macro, pick a name for it, put the name in column A and the code in column B and then call it. If your table, row, and column names are set up properly, then $macro $myMacroName will do it.

Example: you have three checkboxes in known locations (say $A$1, $A$2, and $A$3) and you need to repeatedly check to see if any one checkbox is checked and then perform specific code different for each checkbox. The following in columns A and B of any row in your Macros table will accomplish the goal.

    anyBoxChecked  =IFERROR(CHOOSE(MATCH(TRUE,$A$1:$A$3,0),1,2,3),0)
    runCheckbox    =INDIRECT("$macro $runCheckbox"&$macro $anyBoxChecked)
    runCheckbox0   ="ERROR"
    runCheckbox1   =specific code for checkbox $A$1
    runCheckbox2   =specific code for checkbox $A$2
    runCheckbox3   =specific code for checkbox $A$3

Note the macro statement must always be row and column preserved (i.e. with $s).

This is a complex example (I kind of went to town on it) with macros including macros and indirect macro calls.

But by using $macro $anyBoxChecked<>0 you have a boolean representing if any of the three boxes is checked and by using $macro $runCheckbox you can run checkbox specific code automatically and get an error message if none of the boxes is checked.

Now the bad news: these macros do not accept any parameters and you cannot use any function that is location dependent. So the example you asked for help on, OFFSET($A$2,0,0,ROW()-1,1), can't be one of these macros because ROW() will return the row number of the macro, not the row number of the cell where you called the macro from.

But take heart; I was in exactly the predicament that you were in and using this system in conjunction with some judicious code analysis allowed me to reduce my code footprint substantially and document the remaining code a lot better. It also seems to improve performance.

Another note: you can have the macro rows in any order and add, delete, or move them around as much as you like. Numbers automagically just takes care of the row changes so you don't have to change any macro calls.

Have fun!

(by the way, I am running Numbers 6.0 (6194) on Mojave 10.14.4.

Related Question