+ Reply to Thread
Results 1 to 4 of 4

Dealing with "0" and "-" in Data Validation

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Dealing with "0" and "-" in Data Validation

    I thought I had this worked out but alas my solution only works when addressing empty (blank) cells. I need a solution that will deal with cells that have zero (0 or 0.00) or absolute zero (-) in them. Here's my problem:

    A cell (C1) references two adjacent cells, A1 and B1. A1 or B1 should never BOTH have non-zero values. C1 is calculated by multiplying whichever cell has a non-zero value by a referenced factor. If both A1 and B1 happen to be zero, that's fine, then so is C1. I entered this formula into C1:
    =IF(A1="",B1*Power_Factor,A1*Power_Factor)
    I then added this Data Validation formula into A1 and B1:
    =COUNTA(A1:B1)=1
    The problem lies in the Data Validation formula. I need to have A1 and B1 be able to display "0" (with or without decimals, and absolute zero (-), and blank/empty cells, but still only allow one of them to have a non-zero value. I am certainly open to skipping the data validation approach if there is a clean formula that will work in C1. Any ideas?

    Thanks for your help, as always. This forum is a huge help. David

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,186

    Re: Dealing with "0" and "-" in Data Validation

    Maybe C1:
    Formula: copy to clipboard
    =IF(OR(AND(A1=0,B1=0),AND(A1<>0,B1<>0)),"ERROR",IF(A1<>0,A1*Power_Factor,B1*Power_Factor))



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    05-23-2014
    Location
    Fredericia, Denmark
    MS-Off Ver
    Windows Office 2013, Excel 10
    Posts
    139

    Re: Dealing with "0" and "-" in Data Validation

    That's close but not quite. It works when non-zero values are entered into both A1 and B1 but if zero ("0") is entered into both then I still get the error message. Zero in both A1 and B1 is a possible condition for this spreadsheet.

    Also, as it turns out, cell B1 always as to be either a zero or a positive value. Is it difficult to code that condition?

    Thanks for your help, I really appreciate it. David

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,186

    Re: Dealing with "0" and "-" in Data Validation

    Well, what if you just take out the first bit?

    Formula: copy to clipboard
    =IF(AND(A1<>0,B1<>0),"ERROR",IF(A1<>0,A1*Power_Factor,B1*Power_Factor))


    So, if either or both A1 and B1 is zero it's OK, but if they're both not zero, it's an error.

+ 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. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  2. Replies: 1
    Last Post: 10-23-2013, 05:20 PM
  3. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  4. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  5. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM

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