How to use a formula written as a string in another cell [evaluate for Google Spreadsheet]

user_181940 0
Viewed 28472

I read several old posts about Google Spreadsheet missing the evaluate function. There is any solution in 2016?

The easiest example.

  • 'A1' contains the following string: UNIQUE(C1:C5)
  • 'B1' I want to evaluate in it the unique formula written in 'A1'.

I've tried concatenating in this way: 'B1' containing ="="&A1 but the outcome is the string =UNIQUE(C1:C5). I've also tried the indirect formula.

Any suggestion to break last hopes, please?

Additional note

The aim is to write formulas in a spreadsheet and use these formulas by several other spreadsheets. Therefore, any change has to be done in one place.

google-apps-script formula evaluate google-sheets
2 Answers

I have a solution for my own use case. My investment broker exports data to its users in (badly-formatted) Excel. I do my own analysis in Google Sheets. I have found copy/pasting entire sheets of data to be accident-prone.

I have partially automated updating each tab of the records. In the sheet where I maintain all the records, the First tab is named "Summary"

  1. Save the broker's .xlsx data to Google Sheets (File | Save as Google Sheets);
  2. In the tab named Summary, enter into a cell, say "Summary!A1" the URL of this Google Sheet;
  3. In cell A2 enter: =Char(34)&","&CHAR(34)&"Balances!A1:L5"&Char(34)&")"
  4. In the next tab, enter in cell A1: ="IMPORTRANGE("&Char(34)&Summary!A1&Summary!A2 The leading double quote ensures that the entry is saved as a text string. Select and copy this text string
  5. in cell A3, type an initial "=" + Paste Special.
  6. This will produce an importrange of the desired text, starting at cell A3
user_152636800
Related