Get Formula Text of a Cell on Another Sheet in Excel 2010

Ian3 66 Reputation points
2021-09-04T23:03:50.327+00:00

=FORMULATEXT only works in Excel 2013 and after. Is there a way to do that in Excel 2010? Thanks

Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,671 questions
0 comments No comments
{count} votes

Accepted answer
  1. OssieMac 416 Reputation points
    2021-09-05T00:05:29.653+00:00

    Can do this with VBA (Macro) code using a UDF (User Defined Function)

    To install the VBA code for the UDF:

    1. Alt and F11 to open the VBA editor window
    2. In the VBA editor select menu item Insert -> Module
    3. Copy the VBA code below and paste into the VBA editor.
    4. Close the VBA editor (Cross very top right of VBA editor window)
    5. Save the workbook using "Save as" and set the "Save as type" field to as Excel Macro-enabled Workbook (*.xlsm).

    Ensure macros are enabled as follows.

    1. If Developer ribbon is visible then go to step 4.
    2. If Developer ribbon not visible, Right click anywhere in one of the ribbons and select "Customize the ribbon".
    3. On the right side of the dialog check the box against "Developer" and click OK. (Ensure you click OK and not use cross top right of dialog)
    4. Select Developer ribbon.
    5. Select Macro Security (In the Code block at left end of Developer ribbon).
    6. The dialog that displays should default to Macros in left column.
    7. Select required security option button. (Option to "Disable all macros with notification" should be OK.)
    8. Click OK to close the dialog.

    To use the UDF
    Enter the following formula in a cell where A1 is the cell containing the formula

    =GetFormula(A1)

    or if formula is on a different worksheet then it will be something like as follows with the sheet name in the reference.

    =GetFormula(Sheet1!A1)

    Following is the UDF Code (and Yes! Only 3 lines of code).

    Function GetFormula(rng As Range) As String
    GetFormula = rng.Formula
    End Function

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful