Google Sheets: Tranfer(convert) a text of a formula from one cell to a proper formula in another cel-CodePudding

The question was raised several times with no result - INDIRECT does not work in this case. In Excel EVALUATE function may help, but only using VBA, what also not very usefull.

Range A3:A6 contains conditions, Range B3:B6 - corresponding formulas's texts, E1 contains Condition Selector (Data Validation List in this case). The formula in D9:D12: =indirect("B"&MATCH($E$1,A:A,1))

The question is - how to transfer(convert) the text from B3:B6 to D9:D12, to make them proper formulas?

Please, see a screenshot under this link:

CodePudding user response：

converting text string into valid formula is possible only with script:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName("Contants"); // sheet name  
var src = sheet.getRange("C2");    // The cell which holds the formula
var str = src.getValue();
var cell = sheet.getRange("C10");  // The cell where I want the results to be
cell.setFormula(str);
}

also keep in mind that your string needs to look exactly as formula with = sign in front. see: https://stackoverflow.com/a/60981768/5632629

CodePudding user response：

Player0 - have my sincere thanks - it is really very simple, light and working solution. It works much better, than it made in Excel with it's Evaluate() function. My final code for my particular example was the following:

function onEdit() {
var sheet = SpreadsheetApp.getActive().getSheetByName("Sample"); // 
sheet name
var rowNoCell = sheet.getRange("F1");
var rowNo = rowNoCell.getValue();
var src = sheet.getRange("B" rowNo); // The cell which holds the formula
var str = src.getValue();
var cell = sheet.getRange("D9:D12"); // The cell where I want the r 
esults to be
cell.setFormula(str);
}

It takes corresponded to Condition, selected in F1, cell in the range B3:B6 and post the formula to the range D9:D12.

F1 contains =MATCH($E$1,A:A,1) to link the condition in F1 with values in A3:A6

The only thing that the formula in B3:B6 range has to be a string, without "=" in front.

Thanks a lot once again - the problem solved!

Screenshot