1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Comparing formulas in Excel

Discussion in 'Business Applications' started by dvena, Jul 16, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. dvena

    dvena Thread Starter

    Joined:
    Jul 16, 2010
    Messages:
    2
    Hi,

    This may be a very elementary question. I am not a super sophisticated excel user, but was wondering if there is a way in Excel to compare the formulas (instead of the results) across several worksheets in a workbook. For example, I need to know if the formula in cell A1 on every worksheet is the same.

    Optimally, if one does not equal the test formula, I would like to have the tab name returned.

    Thanks in advance,
    DV
     
  2. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    =FORMULA(A1) returns the formula of the cell A1.

    =IF(FORMULA(Sheet1.A1)=FORMULA(Sheet2.A1),"They match", "Different")
    will tell you whether the two cells A1 have equivalent formulae.

    I'm not sure quite what you want to do with this, so I can't work out what else you need. If you wanted a mass run through of all cells, it might prove necessary to use a macro instead.
     
  3. dvena

    dvena Thread Starter

    Joined:
    Jul 16, 2010
    Messages:
    2
    Thank you.

    I tried it and got an invalid function error. I am running Excel 2007, do you think that might have anything to do with it?
     
  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    In 2003 I get told that FORMULA is not a valid function also.
    One thing you can do is to format the cells that you want to compare as Text, then click in the formula bar, go to the end of the formula and hit enter. You will then see the formula in the cell, rather than the result.
    Do this for the various cells you want to compare. Then use (for example)
    =EXACT(Sheet1!A1,Sheet2!A1)
    to compare A1s on the two different sheets. This will return TRUE if they are the same formula even if the values are different. Change the cell format back to general, click into the formula bar and hit enter and, if the values are different, it will change to FALSE. (See attached.)
     

    Attached Files:

  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  6. Ent

    Ent Trusted Advisor

    Joined:
    Apr 11, 2009
    Messages:
    5,467
    First Name:
    Josiah
    My bad. I forgot I was using OOo, not Microsoft. Big Woops.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/936161

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice