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.

  • Please change the permissions on your sheet to "Anyone with the link can Edit" (not just View), and I or someone else here can better help you. – Erik Tyler Nov 29 at 17:30
  • Ok, allowed to edit. – klor Nov 29 at 17:41

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

  • Unfortunately with this method, I will always have a formula in RED cell, which is what I want to avoid. – klor Nov 30 at 0:17
  • Likely ArrayFormula or ImportRange will be the solution, combined with IFS condition. – klor Nov 30 at 0:31
  • I don't understand what you mean by "Unfortunately with this method, I will always have a formula in RED cell, which is what I want to avoid." There is no formula in the red cell; the formula would go in the blue cell. – Erik Tyler Nov 30 at 3:31
  • Sorry, my mistake. The correct sentence would be the following: "Unfortunately with this method, I will always have a formula in BLUE cell, which is what I want to avoid.". The BLUE cell would have two functions. 1) Automatic value calculation with formula 2) Manual value entering, which needs no formula, just an empty cell. I hope it is clearer now. – klor Nov 30 at 8:20
  • Edited question and added a new example Google Sheet. – klor Nov 30 at 8:47

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.