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?
How to build formula in Google Sheets using content of other cells
google spreadsheets;formulas
Short answerOne 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.WhitespaceSpreadsheet 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.)ParametersWhen 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.