+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Use conditional format to change any value entered in cell to a neg num

  1. #1
    Registered User
    Join Date
    04-27-2007
    Location
    Oldsmar, FL
    MS-Off Ver
    2010
    Posts
    41

    Use conditional format to change any value entered in cell to a neg num

    I'm using Excel 2007 and have a table in a spreadsheet that is used by multiple people and submitted monthly for it to be rolled up to a regional report. In this table, I track opened stores of various types in rows 6-11. In row 12 I track closed stores without any delineation of the type that closes. Row 13 sums up the entries as you would expect ((SUM B6:B11)-B12). The problem is that the number in row 12 has to be entered as a positive and when you glance at the table, it doesn't seem to make sense. I need to be able to change the report to where a negative number is entered and change the sum formula. The problem is that I can't get everyone to enter the number in row 12 as a negative and it messes up the data.

    My question is: can I use conditional formatting to convert the number entered in a cell in row 12 (e.g. B12) to a negative number so I don't have to depend on the multiple users remembering to enter a negative number?

    I've tried using =-ABS(B12) but can't get it to work so I must not understand how to use this in the conditional format set up box.

    Any help would be appreciated.

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Use conditional format to change any value entered in cell to a neg num

    Good day,

    You can restrict values that can be entered in cells in Row12 using 'Data validation' and allowing only 'whole numbers less than or equal to 0'

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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