How to build formula in Google Sheets using content of other cells

Question

I'm working on a Google Sheets document that contains some very long formulas that are getting hard to read and debug. The formulas are long because I often have to select which formula to execute based on some cell value, then wrap that formula in IF and IFERROR statements to prevent bad values from appearing.

Here's an example: I need to perform a lookup on an ephemeral table constructed from a sheet in the document. I choose the sheet to use for building the ephemeral table based on the value of an adjacent cell.

=IF(F3="", , IFERROR(IF(C3<MinMaraDur, HLOOKUP(F3, {QUERY(RankData, "select K,J,I,H,G,F where A='"&$E3&"' and B='"&$B3&"' and C="&$C3&" and D="&$D3&" and E='"&$G3&"'",0);"III","II","I","CMS","MS","MSIC"}, 2), HLOOKUP(F3, {QUERY(MaraRankData, "select J,I,H,G,F,E where A='"&$E3&"' and B='"&$B3&"' and C="&$C3&" and D="&$D3&"",0);"III","II","I","CMS","MS","MSIC"}, 2)), ))

Crazy, right? I want to put pieces of this formula in cells and construct it using those cells' contents, so (1) it's more readable when I edit this sheet again in 3-6 months, and (2) it's easier to debug by testing each part individually.

How can I accomplish that?

Answer 1

Short answer

One can't completely build a formula out of text strings, there is nothing like =formula("=A1+B1") in the Sheets. But one can improve presentation by (a) preparing complex parameters in separate cells, and (b) using whitespace within a formula.

Whitespace

Spreadsheet formulas don't have to be squeezed in one line. The formula bar can be stretched vertically, and linebreaks can be created with Ctrl-Enter (or by preparing the formula in text editor). This already improves readability:

(The linebreak/indents here could be better, this is just a quick example.)

Parameters

When using complex query formulas it is advisable to form query strings separately, so that they can be debugged more easily. So you'll have one cell with

="select K,J,I,H,G,F where 
    A='"&$E3&"' and 
    B='"&$B3&"' and 
    C="&$C3&" and 
    D="&$D3&" and 
    E='"&$G3&"'"

and another with

="select J,I,H,G,F,E where 
    A='"&$E3&"' and 
    B='"&$B3&"' and 
    C="&$C3&" and 
    D="&$D3&""

and then the main formula will refer to those strings. If they are named ranges FirstQuery and SecondQuery, the main formula will be

=IF(F3="", , IFERROR(
   IF(C3<MinMaraDur, 
    HLOOKUP(F3, {
      QUERY(RankData, FirstQuery, 0); 
      "III","II","I","CMS","MS","MSIC"
      }, 2), 
    HLOOKUP(F3, {
      QUERY(MaraRankData, SecondQuery,0); 
      "III","II","I","CMS","MS","MSIC"
      }, 2)
   ), 
))

which I think is pretty readable.