Getting formula of another cell in target cell
How does one cell obtain the formula of another cell as text without using VBA? I can see this question has already been asked many times and the answer is always to write a custom function in VBA.
However, I found a post made in 2006 which claimed to have found the non-VBA solution but the link provided in that post is already broken.
There is nice way of doing this without VBA. It uses XL4 macros (these are macros, but it is not VBA, as asked).
With reference to the figure 1, cells A2:A4 contain usual formulas.
Going to Formulas -> Define Name, I defined two named ranges (see fig. 2), with the information shown in cells A6:B8.
Enter in cell B2
=FormulaAsText. This will retrieve the formula in cell A2 as text.Explanation: The named range
FormulaAsTextuses=GET.CELL(info_type,reference). In this case,ìnfo_type = 6retrieves the formula, andreference = OFFSET(INDIRECT("RC",FALSE),0,-1)uses the cell with 0 rows and -1 columns offset from the one the formula is used in.Copy B2 and paste into B3:B4. This will show formulas in A3:A4. Cell A4 shows that the worksheet function
CELLonly retrieves values, not formulas (as opposed toGET.CELL).Since
FormulaAsTextgets the formula from a cell at fixed offset (0,-1) from the current, I defined another rangeFormulaAsText2, which uses an offset (rows,cols) read from the worksheet itself. Cells D2:D4 contain=FormulaAsText2. Thus, cell D2 shows the contents of cell B3 (=OffSET(D2,1,-2)), which isFormulaAsText. cells D3:D4 show the contents of themselves. This adds some flexibility. YMMV.
PS1: The essence was taken from http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html
PS2: Tim Williams mentioned in a comment "the old XLM GET.FORMULA()". This answer is possibly related (not the same, since this one uses GET.CELL()).
PS3: A simple VBA solution is given, e.g., in http://dmcritchie.mvps.org/excel/formula.htm
EDIT: Complementing this nice answer, the worksheet function FormulaText is available for Excel 2013 and later.
- Largest sum of consecutive values in row, but is it still consecutive in the cell of the last column?
- Dynamically Inserting a row and iterating over it in VBA?
- VBA class member function and class definition problem continues
- How can I perform COALESCE in power query?
- VBA class member function and class definition problem
- How can I set OptionButton font using VBA For Loop?
- VBA remove string custom salutation name from list table excel
- How to import many dataframes from R into a one Excel file in a simple way?
- How to add a missing column to the Transform Sample File
- Pull data from multiple workbooks based on a dynamic list
- Dependent dropdown list in Excel ,Data Validation and VBA
- Excel Formula: Filter Group based on Smallest Values
- Find column name of active cell within ListObject in Excel
- Strange characters found in XML file and PowerShell output after exporting from Excel: ​
- MID function inside COUNTIFS
- Finding Max of 3 Inputs VBA
- How to align duplicates on the same rows in Excel
- How to use .HTMLBody to hyperlink a cell value?
- Adding cell values, specific to each page, to each header
- Excel VBA macro for URL redirect detecting returns 200 instead of 301 sometimes
- Count with specific values in columns
- Convert IEEE Float hex string to an Excel VBA Single (float)
- Openpyxl or Pandas, which is better at reading data from a excel file and returning corresponding values
- Excel Function: Using a specific criteria to form an array to corresponding values
- How to pass metadata to Google Drive API upload with VBA
- IsError not capturing error returned by expression
- Excel VBA multi select drop down list
- Imported Excel file with dates, came out as numbers, trying to return to date format
- Creating Conditional Column based on another Table's Column
- Convert Range to a table