+ Reply to Thread
Results 1 to 5 of 5

Data validation based on formula problem

  1. #1
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Data validation based on formula problem

    I am having a problem with cell validation, but only when a user enters correct data and then clicks off onto another cell.

    For Example in B1 I have the formula =A1+5. In cell A1 I put the following validation =B1>5. This ensures that the value just typed into A1 is a number and that it is greater than 0. When a user enters "1" into A1 and then clicks onto another cell, the validation error comes up, even though 1 is an acceptable value. If the user enters 1 and then presses enter, or tab, or arrow over, then the validation works as expected.

    I realize I could add instructions not to just click off a cell, but that will confuse people and I am trying to make this easy to use for people who rarely use Excel. Is there a way around this problem?

  2. #2
    Registered User
    Join Date
    04-02-2015
    Location
    MO
    MS-Off Ver
    2010
    Posts
    9

    Re: Data validation based on formula problem

    I setup the example you described and got the same results. But when I changed your data validation in A1 to =A1>0 the validation worked even when "clicking out" of the cell.

  3. #3
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Data validation based on formula problem

    I get that same result. My problem is that the value in A1 needs to match a very long list on another tab. I haver an Iferror(Match(),0) formula in B1. So if the user types in something that matches the list, I get the row that item is on, which I need for other calculations on the sheet. If the entry does not match I get 0 and know that I need to trigger the validation error. I know I could use a dropdown selection list, but there are over 100 possible items on the list. Luckily anyone using the sheet will know which area they are in and don't need to see the list to enter in a value.

    I may have to switch to some type of dropdown list because it doesn't look like this is an error that can be fixed. Personally I don't usually click off cells like this, but we have had enough people that do and then get confused, so I have been asked to look into it.

  4. #4
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Data validation based on formula problem

    Error occurs only when b1 has 5 (A1 is zero or empty).
    If you keep A1 1 or greater, this error will not occur
    thanks
    Regards
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju

  5. #5
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Data validation based on formula problem

    Quote Originally Posted by ZJYB View Post
    I setup the example you described and got the same results. But when I changed your data validation in A1 to =A1>0 the validation worked even when "clicking out" of the cell.
    I figured it out with your help. Instead of having the validation formula reference the result of B1, I put the formula from B1 inside the validation for A1. That way the formula is calculated inside the validation instead of outside of it. Your example showed me that it would work. So in my example above my validation formula was =(A1+5)>5. This takes B1 out of the equation. On my actual spreadsheet I just put the Iferror(Match()) formula inside the validation, so I got =Iferror(Match(),0)>0. Now the validation doesn't try to look at the result of B1 anymore, it just calculates that same result in the validation. I realize that is more labor intensive for Excel to do the formula twice, but my spreadsheet is small enough that it doesn't matter.

+ 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] Multiple Data Validation Formula Problem
    By Pooger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-16-2015, 05:55 PM
  2. Problem with Formula combined with data validation
    By taylorbe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-14-2014, 09:02 AM
  3. Problem with Formulas based on Data Validation Selection from multiple tabs
    By warrior2411 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-27-2012, 09:40 PM
  4. Problem with Formula based on Data Validation Selection from multiple tabs
    By warrior2411 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2012, 05:32 PM
  5. Data Validation formula problem
    By rrucksdashel in forum Excel General
    Replies: 1
    Last Post: 09-29-2005, 01:05 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