+ Reply to Thread
Results 1 to 8 of 8

DATA Validation formula - check value of three cells to allow data entry

  1. #1
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    DATA Validation formula - check value of three cells to allow data entry

    I want to write a custom data validation formula which checks the value of three cells.

    For instance -

    The formal applies to cell A1 only. The validation is to make sure Cells A1, and A2 and A3 are all greater than zero (ignore blanks in these cells.)
    Generally - only allow data entry if A1 A2 A3 are greater than 0 and if not STOP and display message data entry values need to be greater than zero, no negative entries allowed.

    Regards,
    -j

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: DATA Validation formula - check value of three cells to allow data entry

    select A1:A3 then
    in custom try =A1>0 and hit ok
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: DATA Validation formula - check value of three cells to allow data entry

    Select A1:A3
    Go to Data Validation, Custom and enter this formula =A1>0 click OK.
    As you enter data in A1 to A3 an error will occur if a value less than 0 is entered. Blank cells are ignored and the error message appears upon entry not after all three cells have been filled.
    Data Entry.JPG
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: DATA Validation formula - check value of three cells to allow data entry

    Thanks for the ideas - clarification of problem-

    I failed to mention Cells A2 and A3 already have other data validations - i.e. Each of the three cells has a unique data validation.

  5. #5
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: DATA Validation formula - check value of three cells to allow data entry

    what sort of validations they are ??

  6. #6
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: DATA Validation formula - check value of three cells to allow data entry

    Custom data validation in Cell A2 & Cell A3:

    Cell A2 will stop data entry in Cell A2 if Cell Z1="F"
    Cell A3 will stop data entry in Cell A3 if Cell Z2="K"

    So really what I'm doing is simplifying my data validation requirements by breaking down the conditions into three separate validation requirements. Cell A1 A2 A3 are three different measurements of the same item... in reality they all have the same validation conditions and will get stopped at one of the three cells if the data validation is not met. That will stop the process since the exercise requires the three readings. Maybe not the slickest approach but it does allow more custom error messages.

  7. #7
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: DATA Validation formula - check value of three cells to allow data entry

    For A1
    A1>0

    in A2
    =(NOT((Z1="F"))+(A2>0)=2)

    it will allow entry when Z1 is not = "F" and A2 is >0

    in A3
    =(NOT((Z2="K"))+(A3>0)=2)

  8. #8
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Boston
    MS-Off Ver
    MS 365
    Posts
    225

    Re: DATA Validation formula - check value of three cells to allow data entry

    Thanks for your time Hemesh and Newdoverman.


    My problem with this solution is my error message can no longer be custom... the message will be now in say Cell A2 when not meeting the validation criteria--- Data is not "F" OR is negative..maybe I'm trying to use the wrong tool - data validation.

    Maybe I'll create some sort of test for Cell A1 A2 A3 if negative pop-up message cant use negatives?

+ 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] Data Validation Conditional Formatting allow entry into only one of two cells
    By dgibney in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-28-2015, 01:44 AM
  2. Replies: 4
    Last Post: 12-19-2013, 10:44 AM
  3. Auto-Populate cells based on data validation and free-form entry
    By Tawana127 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-17-2013, 02:32 PM
  4. Replies: 4
    Last Post: 02-01-2013, 10:18 AM
  5. [SOLVED] Need Data Validation or Formula to Force Entry of Data in .25 Increments
    By sstravs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2012, 07:16 PM
  6. Data validation forces entry in a range of cells.
    By markDuffy in forum Excel General
    Replies: 3
    Last Post: 02-10-2012, 08:46 AM
  7. Replies: 2
    Last Post: 08-30-2011, 03:40 PM

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