+ Reply to Thread
Results 1 to 4 of 4

Conditional format based on cell not equaling the results of a formula

  1. #1
    Registered User
    Join Date
    07-16-2015
    Location
    Montpelier, VT
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    3

    Angry Conditional format based on cell not equaling the results of a formula

    I am building a sheet where users enter data that must foot and cross foot. Using the conditional format (and all examples in this post are only selecting "applies to" a single cell), "format only cells that contain" then Cell Value, not equal to, =c25+d25-e25 and the format highlight fills in red. That way, if someone enters bad data, they'll know if before they send it to me. The problem I'm having is someone enters the formula, then it fine, but if they enter the results, then even if the answer is correct, it fills with red.

    The same thing is happening when I try to "Use a formula to determine which cells to format", format values where this formula is true, =c7+d7-e7-f7<>g7. The same results. If you enter a formula into g7, its fine, if you enter a number that equals the results of the formula, it's not. We want users to enter the raw data, rather than a formula, though we can't stop them from using formulas, we just don't want to build something that forces them to do so.

    Any ideas??

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,445

    Re: Conditional format based on cell not equaling the results of a formula

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    07-16-2015
    Location
    Montpelier, VT
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    3

    Re: Conditional format based on cell not equaling the results of a formula

    Reinvestment Survey FY15 SU 04.xlsxI've noted the cell that I'm having trouble with. It works if you enter a formula, but not if you enter a number, which is what we want users to enter. The exact same condition works elsewhere in the sheet. Any help greatly accepted.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Conditional format based on cell not equaling the results of a formula

    The problem is that computers perform calculations in binary and we calculate with decimals and sometimes there are no exact equivalence. If you take the results of =$C$7+$D$7-$E$7-$F$7 out to multiple decimal places, you get -5126.4999998 which doesn't equal what was entered into G7 so it turns red.

    Fix, change your conditional formating formula to
    =ROUND($C$7+$D$7-$E$7-$F$7,2)
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Format entire row but based on a cell containing a formula
    By C_P in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2014, 10:51 AM
  2. [SOLVED] Conditional format formula producing incorrect results?
    By Dougie12. in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-20-2014, 03:02 PM
  3. Replies: 9
    Last Post: 01-15-2014, 07:59 PM
  4. [SOLVED] Conditional format a cell based on a result of a formula in another cell
    By Taiter in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-20-2013, 04:45 PM
  5. [SOLVED] Conditional format a cell based on a result of a formula in another cell
    By Taiter in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-20-2013, 01:47 PM
  6. Replies: 2
    Last Post: 06-22-2012, 08:51 AM
  7. Replies: 3
    Last Post: 07-18-2011, 02:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1