How do you get the formula from a cell instead of the value?

how do you reference a value instead of formula in a formula in excel?
excel convert formula to value automatically
excel show formula as text in another cell
excel value of cell in formula
excel convert formula to value automatically vba
how to show value instead of formula in excel 2010
replace formulas with their calculated values
excel lock cell value once calculated

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

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

Excel formula showing as text? 3 things you should check to fix the , How do I find the value of a cell in Excel? The following example shows a formula in cell D2 that multiplies cells A2, B2, and a discount derived from C2 to calculate an invoice amount for a sale. To copy the actual value instead of the formula from the cell to another worksheet or workbook, you can convert the formula in its cell to its value by doing the following: Press F2 to edit the

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();
}

[Fixed] Excel formula not showing result, Instead of entering values, you can refer to data in worksheet cells by select the cell reference A2, the formula uses the value of that cell to calculate the result. Type a positive value in one cell, and a negative value in another. In a third cell, use the SUM function to add the two cells together. In this example, cell D6 has the budgeted amount, and cell E6 has the actual amount as a negative number. F6 has the formula =SUM(D6,E6).

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.

Use cell references in a formula - Excel - Office Support, Show Formulas in Excel Instead of the Value in Selected Cells Only. The above methods covered so far would show all the formulas in a worksheet. However, you  Select the cell where you want to show the formula instead of the value. Go to Home –> Find & Select –> Replace (keyboard shortcut – Control + H). In the Find and Replace dialog box, within the replace tab, enter = in the ‘Find what’ field and ‘= in the ‘Replace with’ field.

Use this:function =FORMULATEXT("cell")

How to Show Formulas in Excel Instead of the Values, Workaround. To display the calculated value rather than the formula, you must change the format of the cell containing the formula and re  Cell with formula is locked. Found that the cell I had the formula in was “locked” (Format Cells / Protection) – it would seem that this also prevents the value from showing. De-selecting “Locked” the value appeared immediately. (tip from our reader Clare – thank you!)

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.

Cell linked to text-formatted cell shows formula not value, Trying to form a formula that picks up data from other cells. The values in the other cells are from formulas. eg AA1: formula - 119196. 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.

Formula to read value rather than the formula in the cell, I guess this is the actual answer to the question:- =D1*VALUE(TEXT(C1,"#."​&REPT(0,RIGHT(CELL("format",C1),1)))). i.e. use the CELL function  At times I want to reference the resulting value of the formula for comparison or to feed another formula. Unfortunately, if you are doing a comparison, Excel will try to compare the formula instead of the formulas results. Well, turns out there is an easy way to correct this. If you want your formula result to be a number, then use the VALUE

How to reference Value instead of formula in a formula using Excel , If you change the original constant or any of the cells referred to in the original formula, the result in the cell containing the linking formula is updated at the same  To extract the cell value based row and column numbers, the following formula can do you a favor. Please enter this formula: =INDIRECT(ADDRESS(F1,F2)), and press Enter key to get the result, see screenshot: Note: In the above formula, F1 and F2 indicate the row number and column number, you can change them to your need.

Referring to Values or Formulas in Other Cells in Excel 2010 , Does Excel display the text of your formula in the cell rather than the result as it should? Here is the way to have Excel show the proper result. In Excel, if you apply a formula to a column range, the result will be displayed as zero while the reference cells are blank in the formula. But in this case, I want to keep the cell empty when apply formula until the reference cell entered with data, if there are any tricks to handle it? Keep cell blank until data entered in

Comments
  • I'm still struggling a bit to understand your use case, and would be interested to hear you expand on it a bit. But two things: 1. there is no way of accessing the formula associated with a cell using native spreadsheet functions,and 2. I'm not sure if the INDIRECT function would be of use to you.
  • 1. that answers my question thanks, but how could I access it with the scripting API? 2. INDIRECT is useful once you have the row number, if you want to prefix a specific column, but not to get at the formula. Use Case: I expanded on the question description.
  • So you couldn't have =INDIRECT("A"&A1)+5, =INDIRECT("B"&A1)+10, etc?
  • Almost. One requirement is to be able to change the reference row in only one place. So if "1" is the row you want row 2 to refer to, then you would do B2: =A1, C2: =INDIRECT("C"&B2)+5, D2: =INDIRECT("D"&B2)+10, where A1: =ROW(). Then if you want to change row 2 values to be relative to row 42, then you just change one place B2: =A42.
  • Then perhaps just B2: =ROW(A42)?
  • The first statement is wrong (see your own comment stackoverflow.com/questions/26522305/…) by the other side, the second statement doesn't answer the question, assuming that the title summarises it.
  • In the question, I neglected to emphasize a requirement to do it without adding scripting (using only built-in functions). I did state that in a followup comment: "how can I access it without scripting"? I agree that there are answers stating that it can be done with scripting. Given this, the first statement in this answer is correct - there is no built-in function to get the formula. The second statement is the correct solution for the use case described. Again, the original title was misguided.
  • Although this does not answer the use case of the question, it does perfectly answer the title of the question, which is what brought me here and solved my problem.
  • Formula isn't a Google Sheets function. It works here because it is implemented through the Script Editor as a custom function. function formula(r,c) { return SpreadsheetApp . getActiveSheet() . getRange(r,c) . getFormula() ; }. Too slow to be practical in a large spreadsheet, but does serve to highlight that the problem can be solved with custom script.