In Google Sheets can a formula be built in one cell and activated in another?

Question

I have a relatively complex formula that I am generating in cell G4 (using values from G1-3) and currently pasting the processed value only to cell B1. Is it possible to just get B1 to take the formula to use from G4?

I have tried the simple =G4 and also creating the formula in B1 but this doesn't work.

The formula in G4 is

="{"&$G$1&$G$2&"1,"&$G$3&"Count"&$G$3&";ARRAYFORMULA({UNIQUE(FILTER("&$G$1&$G$2&"2:"&$G$2&","&$G$1&$G$2&"2:"&$G$2&"<>"&$G$3&$G$3&")),ARRAYFORMULA(COUNTIF("&$G$1&$G$2&"2:"&$G$2&",SUBSTITUTE(SUBSTITUTE(UNIQUE(FILTER("&$G$1&$G$2&"2:"&$G$2&","&$G$1&$G$2&"2:"&$G$2&"<>"&$G$3&$G$3&")),"&$G$3&"*"&$G$3&","&$G$3&"~*"&$G$3&"),"&$G$3&"?"&$G$3&","&$G$3&"~?"&$G$3&")))})}"

and it's an interpretation that's copied as value only into B1 is

={RAW_Unique_Attribute_Values!X1,"Count";ARRAYFORMULA({UNIQUE(FILTER(RAW_Unique_Attribute_Values!X2:X,RAW_Unique_Attribute_Values!X2:X<>"")),ARRAYFORMULA(COUNTIF(RAW_Unique_Attribute_Values!X2:X,SUBSTITUTE(SUBSTITUTE(UNIQUE(FILTER(RAW_Unique_Attribute_Values!X2:X,RAW_Unique_Attribute_Values!X2:X<>"")),"*","~*"),"?","~?")))})}

This is pretty straightforward to do in [Apps Script](https://developers.google.com/apps-script/reference/spreadsheet/sheet) if you're open to that; `SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B1").setFormula( "..." )`. — sinaraheneba, Aug 06 '19 at 02:51
Possible duplicate of [Is there a way to evaluate a formula that is stored in a cell?](https://stackoverflow.com/questions/16303680/is-there-a-way-to-evaluate-a-formula-that-is-stored-in-a-cell) — sinaraheneba, Aug 06 '19 at 03:10
@sinaraheneba the solution you have mentioned in the comment using Apps Script seems easy - how would I use that? The link you mentioned seems very complex as against your suggestion so it would be great if you can post this as an answer. Also it's many years since that answer so things may have changed. — GeorgeC, Aug 06 '19 at 04:29
From first glance, things should function the same as explained in the various answers. As this is a duplicate, it would be more appropriate to update those answers as needed. — sinaraheneba, Aug 06 '19 at 05:03

score 0 · Answer 1 · answered Aug 06 '19 at 06:53

any reason why not paste this in B1 cell:

=ARRAYFORMULA({INDIRECT(G1&G2&1), "Count";
 {UNIQUE(FILTER(INDIRECT(G1&G2&"2:"&G2), INDIRECT(G1&G2&"2:"&G2)<>"")),
 COUNTIF(INDIRECT(G1&G2&"2:"&G2), SUBSTITUTE(SUBSTITUTE(
  UNIQUE(FILTER(INDIRECT(G1&G2&"2:"&G2), INDIRECT(G1&G2&"2:"&G2)<>"")), 
 "*", "~*"), "?", "~?"))}})

In Google Sheets can a formula be built in one cell and activated in another?

1 Answers1