0

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?

3
  • Have a look at this Question and the answers as well as the one linked to - may give you an idea... : stackoverflow.com/q/53315486/4961700
    – Solar Mike
    Mar 11, 2019 at 18:38
  • Sorry, I'm confused. The link is to a question on doing what I want but in Excel, and the answer uses VBA, which as far as I know Numbers doesn't support. There is a link there to an article on using EVALUATE, which seems to also be an Excel-only function and only usable in explicit named cells, (which is also I think something Excel supports but Numbers does not). I would love it if any of those worked in Numbers!
    – jetset
    Mar 12, 2019 at 23:52
  • Stackexchange just suggested this question, but it doesn't appear to have an answer, unfortunately. apple.stackexchange.com/questions/27099/…
    – jetset
    Mar 12, 2019 at 23:53

1 Answer 1

1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .