Google-sheets – Set formula into a cell based on content of another cell

formulasgoogle sheetsgoogle-sheets-arrayformulaimportrange

Google Sheets:

I would like to set a formula into a cell, but this should depend on content of another cell.

Example Google Sheet (anybody can edit):
https://docs.google.com/spreadsheets/d/1Z2VVLH5saVKJQ97xUNTZ4vssXnw9Q_ruyQF61tCbxsU/edit?usp=sharing

Goal (old):

The RED "aaa" cell change should set the BLUE "changeme" content to

"aaa", if "aaa"
"", if "nothing"
"=ROUNDUP((1+2)/2)", if "myformula"
"=SUM(1+2)", if "myformula2"

EDIT1:

To make the definition clearer, I created a new worksheet "sheet_new (editable)" on the example Google Sheet:

Goal (new):

The RED cell change should set the BLUE content to:

Formula, if RED cell is set to Automatic
Empty cell, if RED cell is set to Manual

I hope it is clearer now.

Probably using importrange or arrayformula would be the solution.

EDIT2:

If RED cell is set to Manual the user can enter numbers manually to BLUE cell. If RED cell is set to Automatic, the value of BLUE cell will be a formula, which calculates the value.

Best Answer

OK, here is the formula I supplied:

=IFS(B3="aaa","aaa",B3="nothing","",B3="myformula",ROUNDUP((1+2)/2),B3="myformula2",SUM(1+2))

It should be pretty easy to understand (i.e., condition, result, condition, result, etc.).

Related Solutions

Google-sheets – Conditional Formatting based on another sheet’s content

Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then:

Format, Conditional formatting..., Custom formula is:

=match(A1,indirect("Roster"&"!D:D"),0)>0

select yellow (Range: should be A:A) and Save rules.

For each colour you need a separate rule (unless one colour is acceptable by default, so standard formatting applied for that) hence for red for example + Add another rule of:

=match(A1,indirect("Roster"&"!E:E"),0)>0

and repeat for blue (B:B in place of D:D in the first formula) and green (C:C).

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

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:

formula

(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.