How do you get the formula from a cell instead of

Question

How do you get the literal value (the formula) from a cell instead of the result value?

EXAMPLE DESIRED:

• A2: "Foo"
• B3: "=A2" - so it displays "Foo"
• C3: "=CELL("formula", B3)" displays "=A2"

Of course, CELL() doesn't support a "formula" parameter, that's just for demonstrating the intent. I've looked over the function list and nothing jumps out at me.

USE CASE:
I would like to make a reference to another row, and based on that reference get other cells from the row. Putting an explicit row number won't work (e.g. B3 = "2"), since it will not auto-correct when rows are modified. Specifically, I would like for multiple columns in one row to be relative to columns in another row, and be able to change the relative row in one place without having to edit each of those columns.

Normally, to make one row relative to another you would put column values like this: "=A2+5" and "=B2+10". This means that A column is "relative row value +5" and B column is "relative row value + 10". If you want to change the relative row (for example, to row 56), you need to change each of the columns to "=A56+5" and "=B56+10". How to accomplish this by editing just one field?

For a practical example, consider a sheet that is a list of tasks and each one may be marked as "following" another for purposes of computing end dates, but you would like to be able to change the reference task and to support a N:1 relationship between tasks.

[Update]

Actually, I do have a solution to the specific use case. But I am still curious about the original question: getting access to the formula behind the value in a cell.

SOLUTION 1: - A2: "=ROW()" - B2: "Foo" - C3: "=A2" displays "2" and auto-adjusts to maintain reference as rows are added/removed

SOLUTION 2:

Another solution would be to add a unique "id" column and store references by id, then find the row using LOOKUP().

Answer 1

EDIT: This answer is for the use case in the problem description, with the requirement of doing so with only native functions (no scripting).

Based on comments from AdamL to the question, the answer is that you cannot get the formula from a cell (instead of the value).

However, for the actual use case I was trying to solve, =ROW(A42) can be used to get a reference to a specific row that will update automatically with changes to rows.

Answer 2

Try using =formula(2,3) where the coordinates are (row #, col #)

Google sheet example at: https://docs.google.com/spreadsheets/d/1A1l0qdnNSHlJB-5DARGKDeIsbuCCLGuoYWm8sR29UTA/edit?usp=sharing

Showing the formulas...

And here's how it renders...

Note: Sometimes it takes a moment to load the formula, during which time you'll get some sort of error saying "Data loading..." or something.

Also note: I have no idea why this works, since formula is not a listed function for google sheets, but I have been using it in a spreadsheet of mine. Maybe the idea to try this came from Excel or something, I don't recall.

Answer 3

I had the same problem and tried to use the formula that Rubén created, but had a limitation. I couldn't use these formula inside of another one (I was trying to make a mid()). So made a different approach that worked:

function CELLFORMULA(reference) {
  var ss = SpreadsheetApp;
  var sheet = ss.getActiveSheet();
  var formula = ss.getActiveRange().getFormula();
  re = /cellformula\((.*)\);/g;
  args = re.exec(formula);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args + ' is not a valid range');
  }
  return range.getFormula();
}

Answer 4

Use this:function =FORMULATEXT("cell")

Answer 5

Use getFormula() or getFormulaR1C1() methods to get the formula of a cell.

Example adaptated from https://webapps.stackexchange.com/a/92156/88163

The following custom function will return the formula of the referenced cell but if the reference is not valid, it will return an error message.

function CELLFORMULA(reference) {
  var ss = SpreadsheetApp;
  var sheet = ss.getActiveSheet();
  var formula = ss.getActiveRange().getFormula();
  var args = formula.match(/=\w+\((.*)\)/i);
  try {
    var range = sheet.getRange(args[1]);
  }
  catch(e) {
    throw new Error(args[1] + ' is not a valid range');
  }
  return range.getFormula();
}

Example of use of the above custom function

A2: FOO
B3: Formula =A2, value FOO
C3: Formula =CELLFORMULA(B3), value =A2