Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Compare actual formulas (formula verification)

    I need to verify that the formula in a certain cell is the same formula in another cell. EXCEL 2010

    For example


    the formula on tab"LL" Cell L2 is "=CONCATENATE(L2,M2)"
    the formula on tab "Compare Formulas" cell C4 is "=CONCATENATE(L2,M2)"

    I want a formula to verify the formula, not the value generated.

    The following formula only compares the results of the formula. The formulas I want to compare are actually on different tabs and do not have the same results.
    ='Compare Formulas'!C4=LL!L2.

    -Jody
    Last edited by musicteacher98; 2016-06-28 at 23:30. Reason: Updated Excel version

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Consider a UDF to accomplish what you want

    In a standard module, place the following code
    Code:
    Public Function CFormulas(rng1 As Range, rng2 As Range) As Boolean
        CFormulas = rng1.Formula = rng2.Formula
    End Function
    In a cell, add the formula =CFormulas(rng1, rng2) where rng1 and rng2 are the cells that have the formulas you are comparing

    ex =CFormulas(Sheet2!B6,Sheet3!A1)

    HTH,
    Maud

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    musicteacher98 (2016-06-29)

  4. #3
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Resolved:

    I inserted a module with:

    Function FT(MyCell As Range)
    FT = MyCell.Formula
    End Function


    Pulled the values from each of the targeted cells and then compared the value results.

    -Jody

  5. #4
    New Lounger
    Join Date
    Feb 2015
    Posts
    5
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you - While I came up with my own solution in the mean time - yours was _much_ more clean!

    I will add that to my chart.

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Yep, that is another way but you will have additional 2 cells to display the formulas via your UDF then another to compare them. My UDF need only the one cell for the comparison

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •